Fill select results with blank rows

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:

APEX examples