Re: Inverse hierarchical queries

You can use start with ... connect by ... for reverse order. For example,

v734>create table t (curr number, prev number);

Table created.

v734>insert into t values (1,null);

1 row created.

v734>insert into t values (2,1);

1 row created.

v734>insert into t values (3,1);

1 row created.

v734>insert into t values (4,2);

1 row created.

v734>commit;

Commit complete.

Tree in trivial order:

v734>select level, curr, prev from t
  2 start with prev is null connect by prev=prior curr;

     LEVEL CURR PREV
---------- ---------- ----------

         1          1
         2          2          1
         3          4          2
         2          3          1

4 rows selected.

If you want the parents of node 4:

v734>select level, curr, prev from t
  2 start with curr=4 connect by prior prev=curr;

     LEVEL CURR PREV
---------- ---------- ----------

         1          4          2
         2          2          1
         3          1

3 rows selected.

Yves DEBIZET a écrit dans le message <37E9EFE2.1F03BE2E_at_mc2.fr>...
>The "select ;; start with ... connect by ..." is a way to describe a
>hierarchy starting down from a point (root to leaves).
>
>But how to find all the parents of a node in a tree (leaf to root) in a
>single SQL statement ?
>
>Thank you.
>
>--
>Yves DEBIZET
>mailto:yves.debizet_at_mc2.fr
>tel: +33 476 04 50 29
>fax: +33 476 04 50 01
>MC2 (http://www.mc2.fr)
>4 Chemin de Malacher, ZIRST 4401
>F38944 Meylan Cedex
>France
>
>
Received on Thu Sep 23 1999 - 07:21:19 CDT