Fill select results with blank rows

Von Tobias Arnhold 4.14.2010
To fill selects to a specific amount of rows with blank rows. Just use the rownum connect by trick:

-- select template
SELECT *
FROM (SELECT rownum, test_col
FROM test_table t
UNION ALL
SELECT *
FROM (SELECT rownum AS er, 'empty'
FROM dual CONNECT BY rownum <= 99) e
WHERE e.er > (SELECT MAX(rownum) FROM test_table));

-- emp table example for 20 rows
SELECT *
FROM (SELECT rownum, ename, job
FROM emp em
UNION ALL
SELECT *
FROM (SELECT rownum AS er, '-' as er1, '-' as er2
FROM dual CONNECT BY rownum <= 20) e
WHERE e.er > (SELECT MAX(rownum) FROM emp));

I tried the select inside the APEX SQL Workshop:

Post Tags:

2 Comments " Fill select results with blank rows "

Anonymous

Could you list an example of why this logic would be needed?

Anonymous

It;s very usefull