Hello Karol,
thank you, that was the one piece I was looking for, the right position for the
listing.
Best thanks.
Regards.
Olaf
Von: firebird-support@yahoogroups.com <firebird-support@yahoogroups.com>
Gesendet: Mittwoch, 13. November 2019 09:51
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] CTE difficult question
Hi,
from your description i really do not know what is working for you and what is
not working.
And your expectation.
but to understand recursive CTE look at simple sample. Recursive CTE work
throught tree.
####################### metadata ###############################
CREATE TABLE TEST_TREE
(
ID INTEGER NOT NULL,
ID_HEADER INTEGER,
CONSTRAINT PK_TEST_TREE__ID PRIMARY KEY (ID)
);
CREATE INDEX IXA_TEST_TREE__ID_HEADER ON TEST_TREE (ID_HEADER);
####################### test data ###############################
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('1', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('2', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('3', NULL);
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('4', '1');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('5', '4');
INSERT INTO TEST_TREE (ID, ID_HEADER) VALUES ('6', '2');
####################### test query ###############################
WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID AS A, CAST(TT.ID AS VARCHAR(100)) AS ASUM, 0 AS LEVEL
FROM TEST_TREE TT
WHERE TT.ID_HEADER IS NULL
UNION ALL
SELECT TT.ID AS A, RT.ASUM || '_' || TT.ID, LEVEL + 1
FROM TEST_TREE TT JOIN R_TREE RT ON RT.A = TT.ID_HEADER
)
SELECT
*
FROM
R_TREE RT2 INNER JOIN TEST_TREE TT2 ON TT2.ID=RT2.A
###############################################################
run it and then addapt to your needs, as your situation looks same to me
regards,
Karol Bieniaszewski
