Oracle Connect By Prior syntax for recursive query

Question

Suppose I had the following table in my oracle DB:

ID: Name: Parent_ID: 123 a 234 345 b 123 234 c 234 456 d 345 567 e 567 678 f 567 

And what I would like to do is find for each ULTIMATE parent ID ID ULTIMATE parent ID (described as a string, that when you go up, recursively, based on the Parent_ID string, where you finally get this ID = Parent_ID ).

So, for example, 345 parent 123 and 123 parent 234 and 234 parent 234 (which means that this is the top of the chain), so 345 is the ultimate parent 234 - I hope this makes sense ...

So, my result should look like this:

 ID: Name: Ult_Parent_ID: Ult_Parent_Name: 123 a 234 c 345 b 234 c 234 c 234 c 456 d 234 c 567 e 567 e 678 f 567 e 

I just learned about Oracle Connect By statuses today, so this is completely new to me, but I imagine that my query should look like SOMETHING as follows:

 SELECT ID, Name, Parent_ID as Ult_Parent_ID, (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name FROM MyTable t1 CONNECT BY PRIOR Parent_ID = ID; 

Now, as I said, this is my first hit in this kind of SQL - THIS DOES NOT WORK (I get the following error [1]: ORA-01436: CONNECT BY loop in user data and selects the table name in the SQL editor), and I also I don’t know where / how to use the START WITH clause for such requests, but the logic of this seems to me to be correct.

Please help / help point me in the right direction !!!

Thanks!!!

Answer 1

I think CONNECT_BY_ROOT is what you need:

 select x.*, t2.name ultimate_name from ( select t.id, t.name, CONNECT_BY_ROOT parent_id ultimate_id from toto t start with t.id = t.parent_id connect by nocycle prior id = parent_id ) x, toto t2 where x.ultimate_id = t2.id ; 

This gives:

 456 d 234 c 345 b 234 c 123 a 234 c 234 c 234 c 678 f 567 e 567 e 567 e 

Answer 2

Try the following:

 SELECT ID, Name, Parent_ID as Ult_Parent_ID, (SELECT Name from MyTable t2 WHERE t2.ID = t1.Parent_ID) as Ult_Parent_Name, LEVEL FROM MyTable t1 CONNECT BY NOCYCLE Parent_ID = PRIOR ID START WITH Parent_ID = ID; 

I believe that we should use NOCYCLE because of how your roots are defined.

I added the pseudo LEVEL column for illustration purposes only. You should not have this in your final request.

SQL Fiddle with your test data

Answer 3

CONNECT BY will provide you with an immediate parent, but to get the final parent, I would use a recursive subquery . ( CONNECT_BY_ROOT , as explained by Emmanuel, also works)

 WITH r (id, parent, ultimate_parent, name, ultimate_parent_name, lvl) as (SELECT id, parent_id AS parent, parent_id AS ultimate_parent, name, name as ultimate_parent_name, 0 lvl FROM mytable WHERE parent_id = id -- identifies a root UNION ALL SELECT m.id, r.id, ultimate_parent, m.name, r.ultimate_parent_name, r.lvl + 1 FROM r join mytable m on m.parent_id = r.id -- joins child with parent WHERE m.parent_id <> m.id -- to avoid cycles ) SELECT * FROM r ; 

The first part of the subquery retrieves the roots, and the second part connects the children. Parent is the immediate parent and ultimate_parent , the ultimate parent.

Answer 4

create a table test_data (serial number (10), line number (10), parent_line_id number (10));

  insert into test_data values (1000, 101, 100); insert into test_data values (1000, 100, ''); insert into test_data values (3000, 301, 300); insert into test_data values (3000, 300, ''); select * from test_data select * from test_data where order_number in (1000,3000) start with parent_line_id is null connect by prior line_id= parent_line_id