On 23-3-2018 18:26, shg_sistemas@... [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