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...
