Hi Sergio and Mark,
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)
András
From: [email protected]
[mailto:[email protected]]
Sent: Friday, March 23, 2018 6:51 PM
To: [email protected]
Subject: Re: [firebird-support] select *at least* N rows
On 23-3-2018 18:26, [email protected] [firebird-support] wrote:
> Hello! 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...
>
> I hope I'm clear with my question! Not sure if I'm in the "right path",
> but if I can do that I can fix very easily a stored procedure I'm
> working on now.
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.
Mark
--
Mark Rotteveel
__________ Information from ESET Mail Security, version of virus signature
database 17106 (20180323) __________
The message was checked by ESET Mail Security.
http://www.eset.com
[Non-text portions of this message have been removed]
