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
 
 
  • [firebird-supp... 'Check_Mail' check_m...@satron.de [firebird-support]
    • AW: [fire... 'Check_Mail' check_m...@satron.de [firebird-support]
      • AW: [... 'Check_Mail' check_m...@satron.de [firebird-support]
        • R... liviuslivius liviusliv...@poczta.onet.pl [firebird-support]
          • ... 'Check_Mail' check_m...@satron.de [firebird-support]

Reply via email to