Sergio wrote:
Is there any trick to select a fixed number of rows? I mean, no matter if I have 2 rows which match the select, I need to always return 10 rows. Of course the last 8 would be all null in this example...
Mark Rotteveel answers:
There is nothing directly in Firebird to do that, you could try something like this (Firebird 3, for earlier versions use ROWS 10 instead of "fetch first 10 rows only"):
select ID, NAME
from (
-- Need to nest to avoid limitation in the Firebird SQL grammar
select ID, NAME from (
select ID, NAME
from ITEMS
order by id
fetch first 10 rows only
)
-- as many null columns is in the above query
-- repeat the union all as many times as you need guaranteed rows
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
union all select null, null from rdb$database
)
order by id nulls last
fetch first 10 rows only
Technically the order by is not necessary, but leaving it out makes you rely on an implementation detail. If you do add it, the "nulls last" is required.
András Omacht answers:
a bit more general solution (for example max. 100 empty rows without 100 union all):
with recursive
last_empty_row as (
select 100 rownum -- set expected row number here
from rdb$database),
empty_rows as (
select 1 rownum
from rdb$database
union all
select tr.rownum + 1 rownum
from empty_rows tr
where tr.rownum < 100) -- set expected row number here
select first 100 rownum, field1, field2 -- set expected row number here, replace your field names here
from (
-- your real select is coming here…
select first 100 0 rownum, 'A' field1, 'B' field2 -- set expected row number here, replace your field names here
from rdb$database -- replace your table name here
union all
select t.rownum, null field1, null field2 -- replace your field names here
from empty_rows t
cross join last_empty_row l
where t.rownum <= l.rownum
order by 1)
Karol Bieniaszewski answers:
I know that you got the answer but, maybe this trick is interesting for you because it is simplest for use in any query but require creation of one simple procedure. I use it always in situation like you with fixed numbers of rows with nulls
CREATE PROCEDURE GEN_ROWS(IN_COUNT INTEGER) RETURNS (OUT_NO INTEGER)
AS
DECLARE VARIABLE VAR_I INTEGER;
BEGIN
VAR_I=1;
WHILE (VAR_I<=IN_COUNT) DO
BEGIN
OUT_NO=VAR_I;
VAR_I=VAR_I + 1;
SUSPEND;
END
END
and now you can use it in select simply
SELECT
W.*
FROM
GEN_ROWS(10) G
LEFT JOIN YOUR_TABLE W ON G.OUT_NO=1
ORDER BY G.OUT_NO, your_other_fields
ROWS 10