Re: Another Hierarchical Query Question

Hi yitbsal !

Your problem is to order a set of hierarchical rows. I have the same problem.
A solution if is to build a PL/SQL function that build a order string during the select and then select the result from another SQL

SELECT *,
FROM
(SELECT *, order_function (param) new_order   FROM table
  START WITH
  CONNECT BY ...
)
ORDER BY new_order
/

FUNCTION order_function () RETURN VARCHAR2 IS BEGIN
... you build a pseudo order depending on the row, the level after and the current level

END order_function ;

This function must be in a package because you need global variable to store the level before !

Good luck !

<yitbsal_at_statcan.ca> a écrit dans le message : 7ufdh2$kt5$1_at_nnrp1.deja.com...
> 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 - 12:58:32 CDT