sqlselectfirebird

Firebird SQL add empty rows in resultset

How to add empty rows in Firebird SQL Select resultset.

I can create Select, and the select resultset before add 12 empty rows.

1st probe:

select * from table where id like 'R%'
union all
select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null from table

It is add a lot of empty rows (table all recordcount)

2nd probe:

select * from table where id like 'R-%'
union
select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null from table
union
select null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null from table

It is only 1 blank rows.

Solution

  • You can, as you tried, use two queries that both select from your table and use UNION ALL to combine their results. But then add FIRST x in the second query (which will generate "empty rows") to define the number of rows that should be added.

    For example for 12 empty rows and a table with 3 columns:

    SELECT * FROM test WHERE id LIKE 'R%'
UNION ALL
SELECT FIRST 12 null,null,null FROM test;

    Note: This will work fine as long as your table has at least the number of rows you set as number of empty rows. Otherwise, you will get as many empty rows as the table contains rows. Reading your question, I have the impression your table will always have enough rows (because you write you get more emtpy rows than intended when not using FIRST as I showed).

    If you're not sure if your table always has enough rows, but need the fix number of empty rows, rather query x times against the table RDB$DATABASE.

    Of course, the prefered way to implement such tasks is to use your application rather than doing this in SQL.

    See this demo