Re: [firebird-support] select *at least* N rows
Yes, but remember about good join condition then and if needed sort condition especially you can use system table e.g. RDB$RELATION_FIELDS (it contain many records also in empty database) and JOIN on RDB$RELATION_NAME=’RDB$DATABASE’ AND RDB$FIELD_ID=0 instead of “G.OUT_NO=1” regards, Karol Bieniaszewski From: shg_siste...@yahoo.com.ar [firebird-support] Sent: Friday, March 23, 2018 9:03 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] select *at least* N rows That's a very interesting solution as well Karol! Thanks! I've just realized that I can "left join" my table against *any* table which I know it already has enough records for my purpose --- Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast. https://www.avast.com/antivirus
Re: [firebird-support] select *at least* N rows
That's a very interesting solution as well Karol! Thanks! I've just realized that I can "left join" my table against *any* table which I know it already has enough records for my purpose
Re: [firebird-support] select *at least* N rows
Hi, 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 - Best Regards, Karol Bieniaszewski From: shg_siste...@yahoo.com.ar [firebird-support] Sent: Friday, March 23, 2018 7:29 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] select *at least* N rows Thank you very much Mark and András!!! I've tried András solution... I replaced (mechanically, without undestanding too much what was going on...) and it did the trick perfectly! Now is time to study a bit more and try to understand András answer :) Thanks a million again!!! --- Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast. https://www.avast.com/antivirus
RE: [firebird-support] select *at least* N rows
Thank you very much Mark and András!!! I've tried András solution... I replaced (mechanically, without undestanding too much what was going on...) and it did the trick perfectly! Now is time to study a bit more and try to understand András answer :) Thanks a million again!!!
RE: [firebird-support] select *at least* N rows
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: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups..com] Sent: Friday, March 23, 2018 6:51 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] select *at least* N rows On 23-3-2018 18:26, shg_siste...@yahoo.com.ar [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]
Re: [firebird-support] select *at least* N rows
On 23-3-2018 18:26, shg_siste...@yahoo.com.ar [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
[firebird-support] select *at least* N rows
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. Thanks!! Sergio
