I'm seeing some very strange results using _new_connect_by_enabled = true
Login to the database, run these 2 commands:
alter session set "_new_connect_by_enabled" = true;
1 SELECT *
2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid
5* order by parentid, nodeorder
11:22:48 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
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
10 3 1 nested folder2.2.1
11 10 2 nested folder2.2.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
11 rows selected.
11:22:49 rsysdevdb.radisys.com - jkstill@dv01 SQL>
These are the expected results. Now I comment out the 'FROM' clause, and
add a
new FROM and ORDER by:
SELECT *
-- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
from treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
order by parentid, nodeorder;
Here are the results:
SELECT *
2 -- FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 from treenode
4 START WITH parentid=0
5 CONNECT BY PRIOR ID = parentid
6* order by parentid, nodeorder
11:24:21 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
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
10 3 1 nested folder2.2.1
11 10 2 nested folder2.2.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
11 rows selected.
11:24:22 rsysdevdb.radisys.com - jkstill@dv01 SQL>
Hey look! The output is working just the way we would like,
without the inline view.
Now, remove the commented out line:
SELECT *
from treenode
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
order by parentid, nodeorder;
And the resulting output:
1 SELECT *
2 from treenode
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid
5* order by parentid, nodeorder
11:25:12 rsysdevdb.radisys.com - jkstill@dv01 SQL> /
ID PARENTID NODEORDER DESCRIPTION
---------- ---------- ---------- --------------------
1 0 0 top folder
9 1 0 1st subfolder
2 1 1 2nd subfolder
7 1 2 3rd subfolder
4 2 1 folder 2 item 1
3 2 2 folder 2 item 2
6 2 3 folder 2 item 3
10 3 1 nested folder2.2.1
5 7 0 folder 3 item 1
8 7 1 folder 3 item 2
11 10 2 nested folder2.2.2
11 rows selected.
11:25:13 rsysdevdb.radisys.com - jkstill@dv01 SQL>
Interesting, no?
Jared
"Madhavan Amruthur" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/06/2002 08:23 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: SQL Brain Teaser Challenge
Hi Steve,
You can get the below query to work in 8.1.7 (not sure about previous
versions) by setting the undoc parameter _new_connect_by_enabled = true
and can be set for a session
SELECT *
FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
You can also use sys_connect_by_path feature in 9i which gives you the
entire hierarchy path (can be used in 8i with the above undoc parameter)
For eg:
select sys_connect_by_path(parent_id,'/')
from treenode
start with parent_id = 0
connect by prior id = parent_id
will give you a output like
0/1/9
0/1/2
0/1/2/4
...etc
As always setting an undoc parameter is not advisable unless instructed
by Oracle support :-)
Hope this helps.
Regards,
Madhavan
http://www.dpapps.com
--
Madhavan Amruthur
DecisionPoint Applications
--
http://fastmail.fm - The holy hand grenade of email services
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Madhavan Amruthur
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:
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).
