Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
On Fri, 04 May 2018 14:10:40 -0400 blackfalconsoftw...@outlook.com [firebird-support] firebird-support@yahoogroups.com wrote I am hoping to begin writing a technical article soon that will introduce database application developers to these idiosyncrasies who are new to Firebird, making their initial forays into using this database engine less frustrating. I have always believed that these types of issues have kept Firebird from being realized as a top-contender in the database world. It is based upon an excellent technology foundation and should be exposed more generally for the excellent and highly efficient engine that it really is... I hope you follow through on this, Steve! I agree with your observations. Doug C.
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
On 2018-05-04 20:10, blackfalconsoftw...@outlook.com [firebird-support] wrote: > I must admit that I have spent many years working with various > database engines, with SQL Server being the engine where the large > majority of my professional development had been emphasized. > Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little > PostgreSQL and SQLite. In my experience, each and every database engine has their oddities :) [..] > I believe I am finally getting the hang of it. However, one of the > areas that I am finding very confusing is the inconsistencies between > using raw PSQL code in a DB Manager and using it with the Firebird > ADO.NET Provider that one of your project's third-party contributors > provides. PSQL (or procedureal SQL) is specifically the syntax you use in stored procedures, execute block, stored functions and triggers. It is not the syntax that you use when executing queries in your database manager or the Firebird ADO.net driver. That syntax is called DSQL (dynamic SQL). This distinction is important, because some things you can do in PSQL are not possible in DSQL (and vice versa). > For example, if I want to run a "selectable" stored procedure in my DB > Manager, I enter the following for example... > >SELECT * FROM MY_PROCEUDRE_NAME (PARAM) > > However, if you do the same using my own data-access-layer, the > provider yields a parameter mismatch error. There is a very specific > reason for this. I'm not entirely sure what you mean with this. > I have written data access layers for a number of database engines, > including Oracle and PostgreSQL, along with Firebird, which was the > most recent layer I released. In all of layers when SQL code is > passed to a query method, I validate for whether there is a "SELECT" > statement present. If so, than the layer determines that is what is > being passed is raw SQL code. This is because in all cases, procedure > names are simply passed with the name and not with any preceding > "SELECT * FROM" clause. Are you saying your code is trying to parse SQL within your own application? Doing so will likely reject a lot of valid SQL (for example, I wonder what happens if a query including a CTE is passed through that, or something like an execute block). > However, with Firebird's documentation (and the lack thereof for the > ADO.NET provider) developers like myself who are new to Firebird would > naturally assume that the "SELECT * FROM" clause should be included in > that part of the query-string that is passed with the procedure name > to Firebird's ADO.NET Provider, which with my data access layer sees > the query-string as raw SQL code. As far as I'm aware (but haven't verified) the ADO.net driver allows you to pass the stored procedure name, or EXECUTE PROCEDURE procedurename(params) (for an executable procedure) or SELECT * FROM procedurename(params) for a selectable one. > However, after testing the method where the "parameter mismatch" error > was occurring without the "SELECT * FROM" clause, my data access layer > saw the query-string as a stored procedure and ran as expected with > the correct results being returned. Without a specific reproduction case I find it hard to understand what you mean, but it may have to do with the specific handling of the ADO.net driver. You may want to consider posting a question on the mailing list for the ADO.net provider. > I am hoping to begin writing a technical article soon that will > introduce database application developers to these idiosyncrasies who > are new to Firebird, making their initial forays into using this > database engine less frustrating. I'm looking forward to read it! Mark
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
04.05.2018 20:10, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little > PostgreSQL and SQLite. I can't believe that you never looked at Oracle's FOR LOOP syntax in pipelined functions which difference from Firebird is subtle. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Thank you, Mark, for clearing up my misunderstanding. When I found some sample code on StackOverflow that demonstrated the use of the "FOR-DO" construct, I realized what I had misunderstood. I believe I sent you an email that suggested my correction to my understanding of this construct. I must admit that I have spent many years working with various database engines, with SQL Server being the engine where the large majority of my professional development had been emphasized. Nonetheless, I have worked with Oracle, Sybase, MySQL, and a little PostgreSQL and SQLite. I am finding the Firebird Engine to be the most quixotic engine I have worked with to date but after seeing that Microsoft's SQL Server LocalDB Engine was not really suited for embedded database applications (though I wrote a complete installer with the use of the small system API to make the installation as user-friendly as possible), I decided to delve into Firebird with the determination to finally understand its differences when compared to the other engines I have worked with. I believe I am finally getting the hang of it. However, one of the areas that I am finding very confusing is the inconsistencies between using raw PSQL code in a DB Manager and using it with the Firebird ADO.NET Provider that one of your project's third-party contributors provides. For example, if I want to run a "selectable" stored procedure in my DB Manager, I enter the following for example... SELECT * FROM MY_PROCEUDRE_NAME (PARAM) However, if you do the same using my own data-access-layer, the provider yields a parameter mismatch error. There is a very specific reason for this. I have written data access layers for a number of database engines, including Oracle and PostgreSQL, along with Firebird, which was the most recent layer I released. In all of layers when SQL code is passed to a query method, I validate for whether there is a "SELECT" statement present. If so, than the layer determines that is what is being passed is raw SQL code. This is because in all cases, procedure names are simply passed with the name and not with any preceding "SELECT * FROM" clause. However, with Firebird's documentation (and the lack thereof for the ADO.NET provider) developers like myself who are new to Firebird would naturally assume that the "SELECT * FROM" clause should be included in that part of the query-string that is passed with the procedure name to Firebird's ADO.NET Provider, which with my data access layer sees the query-string as raw SQL code. However, after testing the method where the "parameter mismatch" error was occurring without the "SELECT * FROM" clause, my data access layer saw the query-string as a stored procedure and ran as expected with the correct results being returned. I am hoping to begin writing a technical article soon that will introduce database application developers to these idiosyncrasies who are new to Firebird, making their initial forays into using this database engine less frustrating. I have always believed that these types of issues have kept Firebird from being realized as a top-contender in the database world. It is based upon an excellent technology foundation and should be exposed more generally for the excellent and highly efficient engine that it really is... Steve Naidamast
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
On 2018-05-04 18:56, blackfalconsoftw...@outlook.com [firebird-support] wrote: > I am not sure if I understand your explanation here... > > 1... > Why would I test the input parameter, "PI_KEY_IN" for each row? I am > only doing this to ensure that the client never sends an input value > of zero(0). That may be what you want to do, but it is not what your code will actually do. And you may want to consider doing that before producing rows, btw. > 2... > In the other email you sent you stated that... > "A FOR .. DO is a loop construct, which either execute a single > statement for each iteration (each row) in the loop, or a block of > statements delimited by BEGIN and END." > > However. if this follows as most constructs, the "DO" part is the end > statement to the "FOR". Or is this better understood as "FOR" > everything I do here, "DO" the following... ??? No, the DO is not the end of the FOR, it signals that what follows is done for each row produced by the FOR. In short: FOR DO See also: - WHILE: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-while - FOR SELECT: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forselect - FOR EXECUTE STATEMENT: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-forexec Mark
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
I am not sure if I understand your explanation here... 1... Why would I test the input parameter, "PI_KEY_IN" for each row? I am only doing this to ensure that the client never sends an input value of zero(0). 2... In the other email you sent you stated that... "A FOR .. DO is a loop construct, which either execute a single statement for each iteration (each row) in the loop, or a block of statements delimited by BEGIN and END." However. if this follows as most constructs, the "DO" part is the end statement to the "FOR". Or is this better understood as "FOR" everything I do here, "DO" the following... ??? Steve
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
On 4-5-2018 18:31, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > How so? The "SUSPEND" statement is the last statement in the procedure > or are you referring to the fact that it is continued as a result of the > previous "DO" statement? A FOR .. DO is a loop construct, which either execute a single statement for each iteration (each row) in the loop, or a block of statements delimited by BEGIN and END. Mark -- Mark Rotteveel
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
How so? The "SUSPEND" statement is the last statement in the procedure or are you referring to the fact that it is continued as a result of the previous "DO" statement? Steve From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] <firebird-support@yahoogroups.com> Sent: Friday, May 4, 2018 12:25 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure 04.05.2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > If you look at my procedure below, you will note that there is only a single > "SUSPEND" > statement. If you look a little more, you'll see that it is called in a loop. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item Firebird: The true open source database for Windows, Linux ...<http://www.firebirdsql.org/> www.firebirdsql.org Firebird SQL: The true open-source relational database on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
04.05.2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > If you look at my procedure below, you will note that there is only a single > "SUSPEND" > statement. If you look a little more, you'll see that it is called in a loop. -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
On 4-5-2018 18:13, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > However, I am not sure about the multiple "SUSPEND" statements on a > per-record basis. > > If you look at my procedure below, you will note that there is only a single > "SUSPEND" statement. Yet, all of the rows for the entered date that is used > to execute the procedure (select statement follows module code) are returned > as expected (9 rows returned)... > > CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES( >PS_DATE_IN VARCHAR(10) NOT NULL) > RETURNS( >PS_DATE_OUT VARCHAR(10) NOT NULL) > AS > DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL; > BEGIN >PS_SQL = 'SELECT DISTINCT'; >PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG'; >PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM > ML_CREATE_DATE) AS VARCHAR(2)))' || '/'; >PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(DAY FROM > ML_CREATE_DATE) AS VARCHAR(2)))' || '/'; >PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(YEAR FROM > ML_CREATE_DATE) AS VARCHAR(4 = ' || :PS_DATE_IN; >FOR >EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT >DO >SUSPEND; > END; > Nonetheless, would you suggest that I put the "SUSPEND" statement within the > FOR-DO construct? The SUSPEND is already in the FOR-DO construct here. What you are missing is that the above is equivalent to FOR ... DO BEGIN SUSPEND; END In other words, it means "for each row do a suspend", while in your initial question you had FOR ... DO IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; SUSPEND; which is equivalent to FOR ... DO BEGIN IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; END SUSPEND; which means "for each row do throw an exception if PI_KEY_IN = 0, and afterwards suspend a single row" SUSPEND returns the current values of the output fields, and waits for them to be fetched by the client. See also https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-coding.html#fblangref25-psql-suspend Mark -- Mark Rotteveel
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Dimitry ...
Thank you for your reply...
However, I am not sure about the multiple "SUSPEND" statements on a per-record
basis.
If you look at my procedure below, you will note that there is only a single
"SUSPEND" statement. Yet, all of the rows for the entered date that is used to
execute the procedure (select statement follows module code) are returned as
expected (9 rows returned)...
>>>
>>> Stored Procedure
>>>
CREATE PROCEDURE SP_GET_MSGLOG_DISTINCT_DATES(
PS_DATE_IN VARCHAR(10) NOT NULL)
RETURNS(
PS_DATE_OUT VARCHAR(10) NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
PS_SQL = 'SELECT DISTINCT';
PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(DAY FROM ML_CREATE_DATE)
AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || ' ' || '(TRIM(CAST(EXTRACT(YEAR FROM ML_CREATE_DATE)
AS VARCHAR(4 = ' || :PS_DATE_IN;
FOR
EXECUTE STATEMENT (PS_SQL) INTO :PS_DATE_OUT
DO
SUSPEND;
END;
<<<
>>>
>>> SELECT Statement
>>>
SELECT * FROM SP_GET_MSGLOG_RECS ('10/17/2017')
<<<
Nonetheless, would you suggest that I put the "SUSPEND" statement within the
FOR-DO construct?
Thank you...
Steve Naidamast
Sr. Software Engineer
blackfalconsoftw...@outlook.com
[cid:96714d12-5078-4b55-a660-b7d6d4c1b734]
From: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com> on
behalf of Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
<firebird-support@yahoogroups.com>
Sent: Thursday, May 3, 2018 5:17 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Problem returning multiple rows from a
CTE-recursive procedure
03.05.2018 23:04, blackfalconsoftw...@outlook.com [firebird-support] wrote:
> However, I though only the issuance one "SUSPEND" was enough to flush the
> entire buffer of
> all records...
No. One SUSPEND - one record in result set. Two SUSPENDs - two records and
so on.
Watch this in isql:
SQL> set term GO;
SQL> EXECUTE BLOCK RETURNS (A INTEGER)
CON> AS
CON> begin
CON> a = 1;
CON> SUSPEND; -- return a record containing 1
CON> a = 2;
CON> SUSPEND; -- return a record containing 2
CON> SUSPEND; -- return another record containing 2
CON> end
CON> GO
A
1
2
2
--
WBR, SD.
++
Visit http://www.firebirdsql.org and click the Documentation item
Firebird: The true open source database for Windows, Linux
...<http://www.firebirdsql.org/>
www.firebirdsql.org
Firebird SQL: The true open-source relational database
on the main (top) menu. Try FAQ and other links from the left-side menu there.
Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/
++
Yahoo Groups Links
[Non-text portions of this message have been removed]
Re: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
03.05.2018 23:04, blackfalconsoftw...@outlook.com [firebird-support] wrote: > However, I though only the issuance one "SUSPEND" was enough to flush the > entire buffer of > all records... No. One SUSPEND - one record in result set. Two SUSPENDs - two records and so on. Watch this in isql: SQL> set term GO; SQL> EXECUTE BLOCK RETURNS (A INTEGER) CON> AS CON> begin CON> a = 1; CON> SUSPEND; -- return a record containing 1 CON> a = 2; CON> SUSPEND; -- return a record containing 2 CON> SUSPEND; -- return another record containing 2 CON> end CON> GO A 1 2 2 -- WBR, SD. ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
András... Thank you very much for your assistance. Your suggestion seemed to work like a charm. However, I though only the issuance one "SUSPEND" was enough to flush the entire buffer of all records... Steve
RE: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Hi! You should suspend every records like this: DO begin IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; SUSPEND; end András From: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups..com] Sent: Thursday, May 3, 2018 10:33 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Problem returning multiple rows from a CTE-recursive procedure Hello... I have been in the process of converting my application's SQL Server T-SQL code to Firebird PSQL procedures. So far so good until I got to the one where I built a Firebird procedure with a recursive CTE in it. The following code is an exact match to my original SQL Server T-SQL code >>> WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = 3 UNION ALL sp; SELECT RCN2.CN_KEY, RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT * FROM HIERARCHY_TABLE; <<< When run in my Firebird DB Manager within a query script-screen against the two records in the table, it returns both records as it should. The records are setup as follows... >>> record #1 CN_KEY = 3, CN_PARENT_KEY = 0 (a parent key of 0 means that this is the top-most record in the hierarchy) record #2 CN_KEY = 4, CN_PARENT_KEY = 3 (child record to to record #1) <<< The result then from this test is that the Firebird PSQL code produces the same exact result as my SQL Server's T-SQL code when run. The problem I am finding however, is that when I run my Firebird PSQL code above within a procedure, it only returns record #2, the child record, instead of both records. My Firebird PSQL procedure is as follo ws... >>> CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY( PI_KEY_IN BIGINT NOT NULL) RETURNS( PI_KEY_OUT BIGINT, PI_PARENT_KEY_OUT BIGINT) AS BEGIN FOR WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = :PI_KEY_IN UNION ALL SELECT RCN2.CN_KEY, bsp; RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT CN_KEY, CN_PARENT_KEY FROM HIERARCHY_TABLE INTO :PI_KEY_OUT, bsp;:PI_PARENT_KEY_OUT DO IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; SUSPEND; END; <<< Can anyone explain why my procedure is not returning the expected number of records? Thank you... __ Information from ESET Mail Security, version of virus signature database 17326 (20180503) __ The message was checked by ESET Mail Security. http://www.eset.com [Non-text portions of this message have been removed]
[firebird-support] Problem returning multiple rows from a CTE-recursive procedure
Hello... I have been in the process of converting my application's SQL Server T-SQL code to Firebird PSQL procedures. So far so good until I got to the one where I built a Firebird procedure with a recursive CTE in it. The following code is an exact match to my original SQL Server T-SQL code... >>> WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = 3 UNION ALL SELECT RCN2.CN_KEY, RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT * FROM HIERARCHY_TABLE; <<< When run in my Firebird DB Manager within a query script-screen against the two records in the table, it returns both records as it should. The records are setup as follows... >>> record #1 CN_KEY = 3, CN_PARENT_KEY = 0 (a parent key of 0 means that this is the top-most record in the hierarchy) record #2 CN_KEY = 4, CN_PARENT_KEY = 3 (child record to to record #1) <<< The result then from this test is that the Firebird PSQL code produces the same exact result as my SQL Server's T-SQL code when run. The problem I am finding however, is that when I run my Firebird PSQL code above within a procedure, it only returns record #2, the child record, instead of both records. My Firebird PSQL procedure is as follows... >>> CREATE PROCEDURE SP_GET_CAT_CHILD_NODES_BYKEY( PI_KEY_IN BIGINT NOT NULL) RETURNS( PI_KEY_OUT BIGINT, PI_PARENT_KEY_OUT BIGINT) AS BEGIN FOR WITH RECURSIVE HIERARCHY_TABLE AS ( SELECT RCN1.CN_KEY, RCN1.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN1 WHERE RCN1.CN_KEY = :PI_KEY_IN UNION ALL SELECT RCN2.CN_KEY, RCN2.CN_PARENT_KEY FROM RI_CATEGORY_NODES RCN2 JOIN HIERARCHY_TABLE on RCN2.CN_PARENT_KEY = HIERARCHY_TABLE.CN_KEY ) SELECT CN_KEY, CN_PARENT_KEY FROM HIERARCHY_TABLE INTO :PI_KEY_OUT, :PI_PARENT_KEY_OUT DO IF (PI_KEY_IN = 0) THEN EXCEPTION ROOT_CAT_NODE_DELETE; SUSPEND; END; <<< Can anyone explain why my procedure is not returning the expected number of records? Thank you...
