Another Hierarchical Query Question

Hi,

I store an ordered tree of the following form in a table:

                 1
  5         2         44         12
 3 4        6      8 9 10 11    13 14

where 1 is the parent of 5, 2, 44 and 12, 5 is the parent of 3 and 4, 2 is the parent of 6, and so on.

The tree is stored is a table MYTABLE as follows:

LOCAL_ID,PARENT_ID,LEFT_SIBLING
1

5      1
3      5
4      5      3
6      2
44     1      2
8      44
9      44     8
10     44     9
11     44     10
12     1      44
13     12
14     12     13

Note that the order of the siblings, as defined by the LEFT_SIBLING column, is important. The order is NOT defined by the id number itself (ie. note that 44 comes before 12).

Now the following query:

SELECT
LPAD('--------',2*(LEVEL-1)) || LOCAL_ID AS L_ID, PARENT_ID, LPAD('--------',2*(LEVEL-1)) || LEFT_SIBLING AS LEFT_SIB FROM MYTABLE
START WITH LOCAL_ID = 1
CONNECT BY PARENT_ID = PRIOR LOCAL_ID; results in:

LOCAL_ID PARENT_ID LEFT_SIBLING
1
--5 1 --
----3 5 ----
----4 5 ----3
--44 1 --2
----8 44 ----
----9 44 ----8
----10 44 ----9
----11 44 ----10
--12 1 --44
----13 12 ----
----14 12 ----13
--2 1 --5
----6 2 ----

Instead of this, I'd like to get a result as follows:

LOCAL_ID PARENT_ID LEFT_SIBLING
1
--5 1 --
----3 5 ----
----4 5 ----3
--2 1 --5
----6 2 ----
--44 1 --2
----8 44 ----
----9 44 ----8
----10 44 ----9
----11 44 ----10
--12 1 --44
----13 12 ----
----14 12 ----13

Note that this is simply a left depth-first traversal of the tree.

Help?!

Salaam Yitbarek

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Oct 18 1999 - 10:14:51 CDT