CODEWITHSUNDEEP
sqloracle-database
sunleo
sunleo

Reputation: 10943

Oracle Connect By SQL Query

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

Upvotes: 0

Views: 181

Answers (3)

Dba
Dba

Reputation: 6639

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;

Upvotes: 1

Joachim Isaksson
Joachim Isaksson

Reputation: 180917

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

An SQLfiddle to test with.

Upvotes: 3

San
San

Reputation: 4538

Start with is missing

 select *
  from rdt_organization
  start with id = 102  
  connect by prior id = parentid;

Upvotes: 2

Related Questions