Hey Raj,
Ever paranoid of a ruse I had to see for myself that your query worked on
O9i and not O8i. I cranked up my O9i test server and confirmed it does
indeed work on 9i and not 8i. (See below.) I'm still looking for a way to
get this to work on O8i. In the meantime I'll submit a TAR but I hate it
when the "solution" is to upgrade to the next version.
Steve
-------------------------------------------------------------------
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 04:58:14 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
you are logged into
RAC1.
RAC1.SYS.SQL>SELECT *
2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 START WITH parentid=0
4 CONNECT BY PRIOR ID = parentid ;
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.
-----Original Message-----
Sent: Tuesday, November 05, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
Steve,
I did try it ...
oraclei@rhea-ACPT1> sys
SQL*Plus: Release 9.2.0.1.0 - Production on Tue Nov 5 14:18:02 2002
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> conn system
Enter password:
Connected.
SQL> SELECT *
FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
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
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.
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 2:15 PM
To: Jamadagni, Rajendra; '[EMAIL PROTECTED]'
Nope...
SQL> l
1 SELECT *
2 FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
3 START WITH parentid=0
4* CONNECT BY PRIOR ID = parentid
SQL> /
FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
*
ERROR at line 2:
ORA-01472: cannot use CONNECT BY on view with DISTINCT, GROUP BY, etc.
I supplied the DDL/DML so you could test on your own systems.
-----Original Message-----
Sent: Tuesday, November 05, 2002 12:07 PM
To: '[EMAIL PROTECTED]'; Orr, Steve
SELECT *
FROM (SELECT * FROM treenode ORDER BY parentid, nodeorder)
START WITH parentid=0
CONNECT BY PRIOR ID = parentid
Does this work?
Also see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:921234804
9
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!
--
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).
