Hello,

I have a table which data is representing a tree:

CREATE TABLE MyTABLE (
ID INTEGER NOT NULL,
ID_PARENT INTEGER,
GREEN VARCHAR(3),
SortText VARCHAR(5),
CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));

This is the data in this table:

ID	PARENT_ID	GREEN	SortText
1		No	A3
2	1	Yes	B1
3	2	No	C6
4	2	Yes	C5
5	2	Yes	C4
6	1	No	B2
7	6	No	C4
8	6	Yes	C3
9		No	A2
10	9	No	B3
11	10	No	C2
12		No	A1
13	12	Yes	B4
14	13	No	C1

 

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL, 'No', 'A3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes', 'B1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No', 'C7');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes', 'C6');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes', 'C5');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No', 'B2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No', 'C4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes', 'C3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL, 'No', 'A2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No', 'B3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No', 'C2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL, 'No', 'A1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12, 'Yes', 'B4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No', 'C1');

 

Table-Data shown as Tree

A3
	B1
		C7
		C6
		C5
	B2
		C4
		C3
A2
	B3
		C2
A1
	B4
		C1

Now I need a dataset which suspends the green tree-nodes with the complete tree-path for each green cell. This dataset should be ordered alphabetically (A1 before A3 and C5 before C6)

A1
	B4
A3
	B1
		C5
		C6
	B2
		C3

 

I (almost) get the result I want with this statement:

with recursive cte as

  (select id, id_parent, green, SortText

     from MyTable

    where Green = 'Yes'

    union all

   select T2.id, T2.id_parent, T2.green, T2.SortText

     from MyTable T2

          join cte on T2.ID = cte.id_parent)

 

select distinct id, id_parent, green, SortText

  from cte

 

 

 

The dataset is ok, but the ordering is not (because there is no “Depth First by” – Clause)

 

With FB3 I tried to use windows functions (something like below):

Rank() over(Partition by id_parent order by SortText)

 

But I could not solve my problem!  - For instance, I got the message: Recursive member of CTE cannot use aggregate or window function.

Could anybody give me a hint how to solve this.

Thank you,

Josef