Oracle Connect By Prior for Recursive Query Syntax

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 ID the ULTIMATE parent ID (described as row, that when you go up, recursively, based upon Parent_ID the row where you finally get that ID = Parent_ID).

So, for example, 345's parent is 123 and 123's parent is 234 and 234's parent is 234 (meaning it is the top of the chain), therefore 345's ultimate parent is 234 - I hope this makes sense...

So, my result should look as follows:

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 found out about Oracle Connect By statments today, so this is completely new to me, but I'm imagining my query would have to look 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, like I said, this is my first stab at this kind of SQL - THIS DOES NOT WORK (I get the following error [1]: ORA-01436: CONNECT BY loop in user data and it highlights the table name in the SQL editor), and I also don't know where / how to use the START WITH clause for this kind of query, but the logic of it seems correct to me.

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

Thanks!!!

Please explain "does not work". Are you getting any errors or just wrong results? — PM 77-1
– PM 77-1, Commented Oct 16, 2013 at 16:26
Sorry - Bad explanataion - I get the following error [1]: ORA-01436: CONNECT BY loop in user data and it highlights the table name in the query... Sorry about that... I'll also update the Q now — John Bustos
– John Bustos, Commented Oct 16, 2013 at 16:30
Try to add NOCYCLE clause and see whether you agree with the result. — PM 77-1
– PM 77-1, Commented Oct 16, 2013 at 16:31
If you have control over database content it may be to your advantage to have root row with Parent_ID as NULL, i.e. no parent at all. — PM 77-1
– PM 77-1, Commented Oct 16, 2013 at 16:36
Thanks @PM77-1 - Yes, thank you for that tip it worked with the nocycle clause and gave me the correct results... I still don't know how to 1) Make it work for all the records (not only for 1 record to check) and 2)make it work without that clause. But, yes, the data looks correct. Also, unfortunately, I have no control over the DB - I just have query rights... — John Bustos
– John Bustos, Commented Oct 16, 2013 at 16:50

Emmanuel · Accepted Answer · 2013-10-16 17:33:22Z

6

I think the 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

answered Oct 16, 2013 at 17:33

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Bustos Over a year ago

This did the trick!!!! - Thank you very much!!! - Would NEVER have figured this out on my own!!!

PM 77-1 · Accepted Answer · 2013-10-16 18:21:45Z

1

Please try this one:

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 have to use NOCYCLE because of how your roots are defined.

I added pseudo-column LEVEL just for illustration purposes. You do not have to have it in your final query.

SQL Fiddle with your test data

edited Oct 16, 2013 at 18:21

answered Oct 16, 2013 at 17:03

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

4 Comments

John Bustos Over a year ago

Thanks SO MUCH for sticking with me and answering this Q, but I'm still getting that error - [1]: ORA-01436: CONNECT BY loop in user data and it's highlighting the table name in the editor. It goes away if I add in the NOCYCLE clause... Is it possible the DB was configured to not allow this or something???

PM 77-1 Over a year ago

Also in addition to the obvious cycle ( Parent_ID = ID ) your actual database could have multi-row cycles. Like this two-row: 10, 20 and 20,10. If the latter is indeed the case you will have to use NOCYCLE clause.

PM 77-1 Over a year ago

I do not see how to avoid NOCYCLE clause without changing the data.

John Bustos Over a year ago

Thank you so much for your help and advice - I just couldn't get this to work correctly - Even with the NOCYCLE clause... Eventually @Emmanuel's solution proved to give me exactly what I wanted...

Nicolas · Accepted Answer · 2013-10-16 20:10:30Z

1

A CONNECT BY will give you the immediate parent but to get the ultimate 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 fetches the roots and the second part connects the children. Parent is the immediate parent and ultimate_parent, the ultimate parent.

edited Oct 16, 2013 at 20:10

answered Oct 16, 2013 at 17:08

Nicolas

9337 silver badges11 bronze badges

3 Comments

PM 77-1 Over a year ago

CONNECT BY works on any hierarchy. It's what was used in Oracle before recursive factored sub-queries were implemented in Oracle 11.2.

John Bustos Over a year ago

I'm trying this query and getting the error [1]: ORA-00933: SQL command not properly ended with SELECT m.id part highlighted... Does that make any sense to you??

John Bustos Over a year ago

Thanks for your help, but @Emmanuel's solution finally proved accurate for what I wanted. Thank you!!!

Varun Vikram Singh · Accepted Answer · 2014-03-28 06:12:13Z

0

create table test_data (order_number number(10), line_id 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

answered Mar 28, 2014 at 6:12

Varun Vikram Singh

415 bronze badges

Collectives™ on Stack Overflow

Oracle Connect By Prior for Recursive Query Syntax

4 Answers 4

1 Comment

4 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related