Re: Traversing hierarchy with "connect by..." clause

K. Huguley wrote ...

>      I have a table with 2 columns (descendant, ancestor) in which
>      I have stored thesaurus data (narrower term, broader term).
>      Now, I need to retrieve the hierarchy trees using ORACLE's
>      "start with...connect by...." clauses of the SELECT stmt.
>      I have no problems moving down the tree for a specific
>      term (i.e., collecting all BROADER TERM relationships), but I am
>      unable to move up the tree (to collect NARROWER TERM relationships)
>      for that same term.  Do I have to use PL/SQL to accomplish this,
>      or can I do it with a simple SQL stmt?
>      I would appreciate any information - the ORACLE manuals are very
>      vague.
>
>      Thank you,
>      NASA Center for AeroSpace Information

I think a simple SQL stmt can do it.....
..
.. SQL> desc a "My table"
..
.. Name Null? Type
.. ------------------------------- -------- ----
.. PARENT CHAR(10)
.. CHILD CHAR(10)
..
..
.. SQL> select * from a; "My tables contents"
..
.. ROOT
.. ROOT AAA
.. ROOT BBB
.. ROOT CCC
.. AAA AAA-one
.. AAA AAA-two
.. AAA AAA-three
.. BBB BBB-one
.. BBB BBB-two
.. CCC CCC-one
.. CCC CCC-two
.. CCC CCC-three
.. CCC CCC-four
..
.. 13 rows selected.
..
.. SQL> column parent format a25
.. SQL> column child format a25
.. SQL> "moving down the tree"
.. SQL> select level
.. 2 , lpad(' ',2*level)||parent parent
.. 3 , lpad(' ',2*level)||child child
.. 4 from
.. 5 a
.. 6 connect by
.. 7 prior child = parent
.. 8 start with
.. 9 parent is null
.. 10 /
..
.. 1 ROOT
.. 2 ROOT AAA
.. 3 AAA AAA-one
.. 3 AAA AAA-two
.. 3 AAA AAA-three
.. 2 ROOT BBB
.. 3 BBB BBB-one
.. 3 BBB BBB-two
.. 2 ROOT CCC
.. 3 CCC CCC-one
.. 3 CCC CCC-two
.. 3 CCC CCC-three
.. 3 CCC CCC-four
..
.. 13 rows selected.
..
.. SQL> "Moving up the tree"
.. SQL> select level
.. 2 , lpad(' ',2*level)||parent parent
.. 3 , lpad(' ',2*level)||child child
.. 4 from
.. 5 a
.. 6 connect by
.. 7 child = prior parent "look at PRIOR"
.. 8 start with
.. 9 child = 'CCC-two'
.. 10 /
..
.. 1 CCC CCC-two
.. 2 ROOT CCC
.. 3 ROOT
Roel Duijnhouwer email: roel.duijnhouwer_at_pagv.agro.nl

Research Station for Arable Farming and Vegetables Lelystad, The Netherlands. Received on Sun Mar 19 1995 - 18:41:06 CST