Skip to Main Content
  • Questions

  • Full hierarchical tree retrivial from the middle of the tree

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Ofir.

Asked: May 27, 2004 - 8:23 am UTC

Last updated: May 25, 2005 - 3:10 pm UTC

Version: 920

Viewed 1000+ times

You Asked

Hi tom,
your site is amazing! I've been using it for a long time.
I have this question
we have a table (T) that has many trees in it (many hierarchies).
It is in the from of (pk, parent_pk, other fields).
I'm getting a record, which can be anywhere within a tree (top level, leaf, somewhere in the middle) and I need to retrive the entire tree that the record is in.
How can I do it in a single SQL statement?
Thanks,
Ofir

and Tom said...

(always nice to have create tables, sample data to work with...)

but in this case, EMP gives me a good example, we can use it to create three trees pretty easily:

ops$tkyte@ORA9IR2> create table t ( pk int primary key, p_pk references t );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t
2 select empno, decode( mgr , (select empno from scott.emp where ename = 'KING'), to_number(null),
3 mgr )
4 from scott.emp
5 where ename <> 'KING'
6 /

13 rows created.


ops$tkyte@ORA9IR2> select rpad('*',2*level,'*')|| pk
2 from t
3 start with p_pk is null
4 connect by prior pk = p_pk
5 /

RPAD('*',2*LEVEL,'*')||PK
------------------------------------------------------------------------
**7566
****7788
******7876
****7902
******7369
**7698
****7499
****7521
****7654
****7844
****7900
**7782
****7934

13 rows selected.


so, 7566, 7698, 7782 are our "roots", we'll pick an interior, root and leaf node and try them out in that order:


ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x number
ops$tkyte@ORA9IR2> exec :x := 7788

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select rpad('*',2*level,'*')|| pk
2 from t
3 start with pk = ( select substr( max(to_char(level,'fm0000000000')||pk ), 11 )
4 from t
5 start with pk = :x
6 connect by prior p_pk = pk )
7 connect by prior pk = p_pk
8 /

RPAD('*',2*LEVEL,'*')||PK
------------------------------------------------------------------------
**7566
****7788
******7876
****7902
******7369

ops$tkyte@ORA9IR2> exec :x := 7698

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> /

RPAD('*',2*LEVEL,'*')||PK
------------------------------------------------------------------------
**7698
****7499
****7521
****7654
****7844
****7900

6 rows selected.

ops$tkyte@ORA9IR2> exec :x := 7934

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> /

RPAD('*',2*LEVEL,'*')||PK
------------------------------------------------------------------------
**7782
****7934


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great

Ofir, May 27, 2004 - 10:13 am UTC

thanks very much Tom for the quick and great response!
Sorry for not attaching a test case - this was a very broad question, so I didn't think I need to put some table format. Your EMP example is just what I meant.
Well, concatenating the level with the PK to get the top of the tree and then extracting the PK is clean and elegant solution...I knew I should use max and level in a subquery, but didn't figure out just how to tie the ends (-:
I'll use just that, so thank again
Ofir

What about the specific subtree only?

Basil, May 27, 2004 - 11:04 am UTC

In the example for node 7788, the results include not only 7788, but the trees for 7788's siblings. What if, in this example, the output should only include
**7566
****7788
******7876

That is, the parents of 7788, along with 7788's children (but not 7788's siblings).

Tom Kyte
May 27, 2004 - 8:21 pm UTC

you could use a "walk up" with a "walk down"

one would start with pk = :x connect by prior p_pk = pk
the other start with pk = :x connect by prior pk = p_pk



Pretty Cool

A reader, May 27, 2004 - 3:09 pm UTC


Orac 9 only?

Chris, May 25, 2005 - 11:51 am UTC

This solution is exactly what I am looking for, but I keep getting 'ORA-01436: CONNECT BY loop in user data' errors. Do I have a bug in my query or is the error a result of working in Oracle8i?

My table is of the same structure with only different names. (PK is now PL_ID, and P_PK is now REF_PL_ID).

My Query:
select rpad('*',2*level,'*')|| pl_id
from pl_network_analysis
start with pl_id = ( select substr( max(to_char(level,'fm0000000000')||pl_id),11 )
from pl_network_analysis
start with pl_id = 521
connect by prior ref_pl_id = pl_id )
connect by prior pl_id = ref_pl_id;



Tom Kyte
May 25, 2005 - 3:10 pm UTC

it means you have a loop in your data basically.

when you start with pl_id = 521 and connect by your criteria -- eventually we are coming back to a value we already visited and you have the proverbial infinite loop.



"walk up" with a "walk down" ??

Dev, January 16, 2007 - 1:41 am UTC

What do you mean by "walk up" with a "walk down". Do we require union of two queries to get the desired output

like

**7566
****7788
******7876

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library