Note 1009345.6 on MetaLink provides a solution
similar to the one proposed with an index.
Jared
On Tuesday 05 November 2002 09:24, Orr, Steve wrote:
Challenge: present SQL results hierarchically and sort the nodes. Use sort
column without changing data. Here's the DDL/DML to start:
create
based on the date it was written, that was before hints were available
for use. so it makes sense to include the dummy comparison to the
indexed column in the where clause as described in the note.
You mean Oracle sometimes thinks of these things BEFORE we do? nah!
--- Jared Still [EMAIL
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
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
]
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
Hi Steve/Jared,
It was coincidence that just when Steve posted this we were trying to
flatten the hierarchy and PL/SQL was the only option and I was doing some
reserach and stumbled upon this parameter and then I used to it to
generate output using the sys_connect_by_path and then all I had to do
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
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
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
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 =
]
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
Title: RE: SQL Brain Teaser Challenge
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
I get an error on 8.1.7.2. Is siblings new?
SQL l
1 SELECT LEVEL, treenode.*
2FROM 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
Does an in-line view do the trick?
select * from
(select id, parentid, nodeorder, description
from treenode
start with parentid=0 connect by prior id = parentid)
order by nodeorder;
Jay
[EMAIL PROTECTED] 11/05/02 12:24PM
Challenge: present SQL results hierarchically and sort the nodes. Use
Steve,
This works for me.
Jared
col nodelevel noprint
col parent noprint
col child noprint
select
a.nodelevel
, a.id id
, a.parentid
, a.nodeorder
, a.description
, decode(c.children,null,'N','Y') parent
Title: RE: SQL Brain Teaser Challenge
Steve,
I'm busted! You caught me cheating. Still you must admit it was an interesting use of TRANSLATE given the data in the DESCRIPTION field.
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original
Steve,
This works for me.
Jared
col nodelevel noprint
col parent noprint
col child noprint
select
a.nodelevel
, a.id id
, a.parentid
, a.nodeorder
, a.description
, decode(c.children,null,'N','Y') parent
I love a good challenge. Since you cannot sort on a hierarchical query, you
have to use an inline query...
select id, parentid, nodeorder, description
from (select id, parentid, nodeorder, description from treenod
start with parentid=0 connect by prior id = parentid)
order by parentid,
Title: RE: SQL Brain Teaser Challenge
select *
from treenode
order by translate(description || 0, '0123456789abcdefghijklmnopqrstuvwxyz ', '0123456789')
Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Orr
How about...
SELECT t.id
, t.parentid
, t.nodeorder
, t.description
FROM treenode t
CONNECT BY t.parentid = PRIOR t.id
START WITH t.description = 'top folder'
ORDER BY NVL (
TRIM (
TRANSLATE (
LOWER
Title: RE: SQL Brain Teaser Challenge
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
Title: RE: SQL Brain Teaser Challenge
Whoa... A query that works on 9i but fails on 8i
!!!
-Original Message-From: Jamadagni, Rajendra
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, November 05,
2002 12:19 PMTo: Orr, Steve;
'[EMAIL PROTECTED]'Subject: RE: SQL Brain Teaser
You still haven't answered the challenge... your results are in a different
order than the desired results I gave at the bottom. The idea is to have the
children appearing immediately after the parents in the correct order.
-Original Message-
Sent: Tuesday, November 05, 2002 11:57 AM
Well it worked but it doesn't follow the rules. The description can change
so it should not be sorted on. Consider this:
update treenode set description='2nd item, 3rd folder' where id=8;
select * from treenode
order by translate(description || 0,
'0123456789abcdefghijklmnopqrstuvwxyz ',
Title: RE: SQL Brain Teaser Challenge
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:9212348049
Raj
Nope...
(select id, parentid, nodeorder, description
from treenode
start with parentid=0 connect by prior id = parentid)
order by nodeorder;
ID PARENTID NODEORDER DESCRIPTION
-- -- --
1 0 0 top folder
9
Nope...
SQL l
1 SELECT *
2FROM (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
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
Well it works but your query assumes knowledge of the tree- that it will
always only have 3 levels. Consider when I add the following 2 rows:
insert into treenode values(10,3,1,'nested folder2.2.1');
insert into treenode values(11,10,2,'nested folder2.2.2');
Now it fails. SORRY I wasn't clear on
and i got this to work:
SQL select * from treenode;
ID PARENTID NODEORDER DESCRIPTION
-- -- --
1 0 0 top folder
9 1 0 1st subfolder
2 1 1 2nd subfolder
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.*
2FROM treenode
3 START WITH parentid=0
4
Okay, my answer was almost correct (almost correct = wrong). Jared's answer
is right on, given the current data set.
What happens when the data is changed? Does ID have meaning or is it the
sequence in which the row was added? NODEORDER is 'sequential', but the
starting values vary within the
Other posts should answer most of your questions. Jared's answer assumes
only three levels and doesn't work when we add levels or branches to the
tree. ID is a system generated key and has no meaning. SQL only, no SQL*Plus
stuff. NODEORDER only refers to the sort within a node because you should
I wondered if someone would try that. :-)
The link you provided is interesting but I don't think it fits the needs as
regards being able to add and rearrange nodes. (Maybe I got confused with
the little worms.) This is because the right column has to be twice the
number of rows and this number
Steve,
I'm not sure why as of yet, but I had some success by creating two segmented
indexes. One on PARENTID, NODEORDER and the second on NODEORDER, PARENTID:
ID PARENTID NODEORDER DESCRIPTION
1 00 top folder
9 10 1st subfolder
2 11 2nd
actually - i think you are correct. the Nested-Set Model of Trees as
Celko calls it would require more maintenance than the traditional parentid
column when the tree changes... but the advantages on the query side are
interesting.
that nodeOrder column takes you on a ride. looks to me like
:
Sent by: Subject: RE: SQL Brain Teaser Challenge
[EMAIL PROTECTED
PROTECTED]
.comcc:
Sent by: Subject: RE: SQL Brain Teaser
Challenge
[EMAIL PROTECTED]
om
06/11/2002
06:49
WOW. This actually works quite well!!!
All this business is for generating an HTML tree navigation object using
SQL, Perl-CGI, and javascript (from www.treeview.net) and it's critical that
everything appear in the right order or the javascript will fail miserably.
I hate to be dependent on
: RE: SQL Brain Teaser
Challenge
[EMAIL PROTECTED]
om
06/11/2002
06:49
Please respond
to ORACLE-L
Well it works but your query
PROTECTED] To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
om cc:
Sent by: Subject: RE: SQL Brain Teaser Challenge
@rightnow To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
.comcc:
Sent by: Subject: RE: SQL Brain Teaser Challenge
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
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
Subject:RE: SQL Brain Teaser Challenge
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
45 matches
Mail list logo
