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, Oct 16 '13 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, Oct 16 '13 at 16:30
Try to add `NOCYCLE` clause and see whether you agree with the result. — PM 77-1, Oct 16 '13 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, Oct 16 '13 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, Oct 16 '13 at 16:50

score 5 · Accepted Answer · answered Oct 16 '13 at 17:33

5

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 '13 at 17:33

Emmanuel

13,935
12
50
72

This did the trick!!!! - Thank you very much!!! - Would **NEVER** have figured this out on my own!!! – John Bustos Oct 16 '13 at 20:42

PM 77-1 · Answer 2 · 2013-10-16T18:21:45.470

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 '13 at 18:21

answered Oct 16 '13 at 17:03

PM 77-1

12,933
21
68
111

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??? – John Bustos Oct 16 '13 at 17:14
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 Oct 16 '13 at 17:33
I do not see how to avoid `NOCYCLE` clause without changing the data. – PM 77-1 Oct 16 '13 at 18:22
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... – John Bustos Oct 16 '13 at 20:44

Nicolas · Answer 3 · 2013-10-16T20:10:30.063

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 '13 at 20:10

answered Oct 16 '13 at 17:08

Nicolas

923
7
11

`CONNECT BY` works on any hierarchy. It's what was used in Oracle before recursive factored sub-queries were implemented in Oracle 11.2. – PM 77-1 Oct 16 '13 at 18:11
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 Oct 16 '13 at 19:58
Thanks for your help, but @Emmanuel's solution finally proved accurate for what I wanted. Thank you!!! – John Bustos Oct 16 '13 at 20:45

score 0 · Answer 4 · answered Mar 28 '14 at 06:12

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

Oracle Connect By Prior for Recursive Query Syntax

4 Answers4