Oracle-L: RE: SQL Brain Teaser Challenge

From: Orr, Steve <sorr_at_rightnow.com>
Date: Thu, 07 Nov 2002 07:23:51 -0800
Message-ID: <F001.004FDC43.20021107072351@fatcity.com>

Thanks Kevin, good to hear from you. As usual you're Johnie on spot with TFM. It's interesting that this can be overcome with the inline view technique posted earlier by Raj.

Steve

-----Original Message-----
Sent: Wednesday, November 06, 2002 5:23 AM To: Multiple recipients of list ORACLE-L

Directly from TFM....

Notes on Hierarchical Queries:

If you specify a hierarchical query and also specify the ORDER BY clause, the ORDER BY clause takes precedence over any ordering specified by the hierarchical query, unless you specify the SIBLINGS keyword in the ORDER BY clause.

The manner in which Oracle processes a WHERE clause (if any) in a hierarchical query depends on whether the WHERE clause contains a join:

If the WHERE predicate contains a join, Oracle applies the join predicates before doing the CONNECT BY processing.
Oracle applies any non-join predicates (that is, all predicates if the WHERE clause does not contain a join) after doing the CONNECT BY processing without affecting the other rows of the hierarchy.

-----Original Message-----
Sent: Tuesday, November 05, 2002 3:29 PM To: Multiple recipients of list ORACLE-L

I was also able to confirm this works on O9i.

-----Original Message-----
Sent: Tuesday, November 05, 2002 11:14 AM To: Multiple recipients of list ORACLE-L

I get an error on 8.1.7.2. Is "siblings" new?

SQL> l
  1 SELECT LEVEL, treenode.*
  2 FROM treenode
  3 START WITH parentid=0
  4 CONNECT BY PRIOR ID = parentid
  5* ORDER SIBLINGS BY PARENTid , nodeorder SQL> /
ORDER SIBLINGS BY PARENTid , nodeorder

*
ERROR at line 5:
ORA-00924: missing BY keyword

-----Original Message-----
Sent: Tuesday, November 05, 2002 11:02 AM To: 'ORACLE-L_at_fatcity.com'; Orr, Steve

SELECT LEVEL, treenode.*
FROM treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
ORDER SIBLINGS BY PARENTid , nodeorder
Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Tuesday, November 05, 2002 12:24 PM To: Multiple recipients of list ORACLE-L

Challenge: present SQL results hierarchically and sort the nodes. Use sort column without changing data. Here's the DDL/DML to start: create table treenode (

        id              number          not null 
                        constraint pk_treenode primary key, 
        parentid        number          not null, 
        nodeorder       number          not null, 
        description     varchar2(20)    null); 
insert into treenode values(1,0,0,'top folder'); 
insert into treenode values(9,1,0,'1st subfolder'); 
insert into treenode values(7,1,2,'3rd subfolder'); 
insert into treenode values(2,1,1,'2nd subfolder'); 
insert into treenode values(8,7,1,'folder 3 item 2'); 
insert into treenode values(6,2,3,'folder 2 item 3'); 
insert into treenode values(5,7,0,'folder 3 item 1');

insert into treenode values(3,2,2,'folder 2 item 2'); insert into treenode values(4,2,1,'folder 2 item 1');

Here's the data presented hierachically without the desired sort: select * from treenode
start with parentid=0 connect by prior id = parentid;

ID PARENTID NODEORDER DESCRIPTION

---------- ---------- ---------- -------------------- 
         1          0          0 top folder 
         9          1          0 1st subfolder 
         7          1          2 3rd subfolder 
         8          7          1 folder 3 item 2 
         5          7          0 folder 3 item 1 
         2          1          1 2nd subfolder 
         6          2          3 folder 2 item 3 
         3          2          2 folder 2 item 2 
         4          2          1 folder 2 item 1 
----------------------------------------------------- 
Desired SQL statement results: 
        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 
-----------------------------------------------------

Kudos to anyone who can figure out how to do this via SQL.

Steve Orr
Bozeman, Montana
--

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).
--

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
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------