I have the following use case where I want to make use of hierarchical queries to get the desired result.
In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is its ID and Type.
Now the problem is when two nodes with same Id and different type, when I call connect by clause only on id I get child for other types of nodes also (if id of that node is same).
create table TreeTest (
nodeid integer,
nodetype char(1),
parentid integer,
parenttype char(1)
);
data in table:
1, 'A', NULL, null
2, 'A', 1, 'A'
3, 'A', 1, 'A'
2, 'B', NULL, null
3, 'B', 2, 'B'
Now I am firing the following query (which is not giving the correct result)
SELECT * FROM TREETEST
START WITH PARENTID = 1
CONNECT BY PRIOR nodeid = PARENTID;
I tried following query also, but again wrong result
SELECT * FROM TREETEST
START WITH PARENTID = 1 AND PARENTTYPE = 'A'
CONNECT BY PRIOR nodeid = PARENTID AND NODETYPE = PARENTTYPE;
Kindly provide the correct query and kindly also explain why second query is not working.
Expected output:
2 A 1 A
3 A 1 A