Here I want to take all the children including parent using parentid what is wrong.Am getting Exception connect by in loop user data. Thanks in Advance.
Query :
select *
from rdt_organization
connect by prior parentid = 102;
Table Content :
id parentid
102 null
103 102
112 102
104 103
105 null
106 105
Output Expected :
id parentid
102 null
103 102
112 102
104 103
You need to connect the rows to the PRIOR row using id and parentid, and use START WITH to decide where to start;
SELECT *
FROM rdt_organization
START WITH id = 102
CONNECT BY PRIOR id = parentid
Start with is missing
select *
from rdt_organization
start with id = 102
connect by prior id = parentid;
Add START WITH id = 102 in the query and try,
WITH rdt_organization(ID, parentid) AS
(
SELECT 102, NULL FROM DUAL
UNION ALL
SELECT 103,102 FROM DUAL
UNION ALL
SELECT 112, 102 FROM DUAL
UNION ALL
SELECT 104, 103 FROM DUAL
UNION ALL
SELECT 105, NULL FROM DUAL
UNION ALL
SELECT 106, 105 FROM DUAL
)
SELECT *
FROM rdt_organization
START WITH id = 102
CONNECT BY PRIOR ID = parentid;
