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