Subject AW: AW: [firebird-support] CTE difficult question
Author Check_Mail
Post date 2019-11-13T15:08:24Z

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