Ooops. Was rather late (or rather early) when I sent this. Obviously the ascending CONNECT BY subquery should not be in the WHERE clause but in the descending CONNECT BY to stop recursion.
>----- Original Message ----- >From: "Orr, Steve" <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Tue, 05 Nov 2002 16:43:26 > >> I have a solution which doesn't rely on hints, >but I am not very >> satisfied with it either. > >Innovative nonetheless. Another cool way to skin >this cat. >Thanks! >Steve > > >-----Original Message----- >Sent: Tuesday, November 05, 2002 5:14 PM >To: Multiple recipients of list ORACLE-L >Importance: High > > >"Orr, Steve" wrote: >> >> > What do I win? >> This was stated in the very first post... kudos. >:-) >> At the moment you and Rich Jesse are tied but I'm >still not very pleased >> with the solution. But unless somebody comes up >with something better I'll >> box you up some kudos for shipping. (I afraid to >ask but what are kudos >> anyway?) >> >> Thanks. >> >> Tentatively yours, >> Steve >> > >Steve, > > I have a solution which doesn't rely on hints, >but I am not very >satisfied with it either. It relies on a function, >and performance will >be likely to be dismal if your tree grows big. >Here is the function : >create or replace function tree_rank(p_id in >number) >return number >is > n_rank number; >begin > select sum(nodeorder * power(10, -1 * level)) > into n_rank > from treenode > where id in (select id from treenode > connect by id = prior parentid > start with id = p_id) > connect by parentid = prior id > start with id = 1; > return n_rank; >end; >/ > >(double 'CONNECT BY', ouch). Note that if you >expect more than 10 items >per level, you should use somthing bigger than 10 >in the power function. > >However : > >SQL> select * from treenode > 2 order by tree_rank(id); > > ID PARENTID NODEORDER DESCRIPTION >---------- ---------- ---------- >-------------------- > 1 0 0 top folder > 9 1 0 1st subfolder > 2 1 1 2nd subfolder > 4 2 1 folder 2 item 1 > 3 2 2 folder 2 item 2 > 6 2 3 folder 2 item 3 > 7 1 2 3rd subfolder > 5 7 0 folder 3 item 1 > 8 7 1 folder 3 item 2 > >9 rows selected. > >-- >Regards, > >Stephane Faroult >Oriole Software >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.com >-- >Author: Stephane Faroult > INET: [EMAIL PROTECTED] > >Fat City Network Services -- 858-538-5051 >http://www.fatcity.com >San Diego, California -- Mailing list and >web hosting services >To REMOVE yourself from this mailing list, send an >E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >the message BODY, include a line containing: UNSUB >ORACLE-L >(or the name of mailing list you want to be removed >from). You may >also send the HELP command for other information >(like subscribing). >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.com >-- >Author: Orr, Steve > INET: [EMAIL PROTECTED] > >Fat City Network Services -- 858-538-5051 >http://www.fatcity.com >San Diego, California -- Mailing list and >web hosting services >To REMOVE yourself from this mailing list, send an >E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >the message BODY, include a line containing: UNSUB >ORACLE-L >(or the name of mailing list you want to be removed >from). You may >also send the HELP command for other information >(like subscribing). >--------------------------------------------------- >------------------ >--------------------------------------------------- >------------------ Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
