RE: RE: SQL Brain Teaser Challenge

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 06 Nov 2002 00:08:27 -0800
Message-ID: <F001.004FC6FD.20021106000827@fatcity.com>

('binary' encoding is not supported, stored as-is)
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" <sorr_at_rightnow.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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: sfaroult_at_oriole.com
>
>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: ListGuru_at_fatcity.com (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: sorr_at_rightnow.com
>
>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: ListGuru_at_fatcity.com (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: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).

Received on Wed Nov 06 2002 - 02:08:27 CST