Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL Query Treewalk Teaser

Re: SQL Query Treewalk Teaser

From: Mike Cretan <mcretan_at_ozemail.com.au>
Date: Tue, 30 Apr 2002 10:06:48 +1000
Message-ID: <Oclz8.14$f%.11565@vicpull1.telstra.net>


The trouble with connect by is that it only traverses the tree up or down, not the whole tree.

Say I have the following scenario:

   ID1      ID2
     |           |
     --------
           |
         ID3
           |
     --------
     |           |
   ID4      ID5
     |

   ID6

I have created this with the following test case:

SQL> desc tree

 Name                            Null?    Type
 ------------------------------- -------- ----
 ID                                       NUMBER
 LINK_ID                                  NUMBER

SQL> select * from tree
  2 /

       ID LINK_ID
--------- ---------

        1         3
        2         3
        3         4
        3         5
        4         6
        5
        6

Now say my starting ID is ID4, I can get the values up the tree by doing the following:

SQL> l
  1 select level, ID
  2 from tree
  3 connect by prior ID = link_Id
  4* start with ID = 4
SQL> /     LEVEL ID
--------- ---------

        1         4
        2         3
        3         1
        3         2

And I can get the values down the tree by doing this...

SQL> select level, ID
  2 from tree
  3 connect by ID = prior link_Id
  4 start with ID = 4
  5 /

    LEVEL ID
--------- ---------

        1         4
        2         6

But if I want to get ALL values in the tree, how do I do it?

I tried the following query which should have fired off the downward tree walk for each value up the tree, but alas to no avail...

  1 select level, ID
  2 from tree
  3 connect by ID = prior link_Id
  4 start with ID = (select ID from tree

  5                    connect by prior ID = link_Id
  6*                     start with id = 4)
  7 /
 start with ID = (select ID from tree
                   *

ERROR at line 4:
ORA-01427: single-row subquery returns more than one row

I'd really appreciate some help

Regards,

Mike Cretan

"Ujjwalc" <ujjwalc_at_aol.com> wrote in message news:20020429014638.24174.00006310_at_mb-mi.aol.com...

> Have you aready considered the
> "connect by" ?
>
> Put some sample data , tables and the
> output you woud like -- may be
> somene will reply.
>
> Regards
>
Received on Mon Apr 29 2002 - 19:06:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US