Home » SQL & PL/SQL » SQL & PL/SQL » need hierarchical list from root node to leaf node; reading left to right (V10.2.0.4 RHAS4)
need hierarchical list from root node to leaf node; reading left to right [message #347775] |
Sat, 13 September 2008 20:27  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
drop table orgnode;
create table orgnode (
orgnodeid number(10),
description varchar2(32),
parentid number(10));
INSERT INTO ORGNODE VALUES(41801,'081350',40955);
INSERT INTO ORGNODE VALUES(40955,'157CFO',40954);
INSERT INTO ORGNODE VALUES(40954,'CFOORG',40859);
INSERT INTO ORGNODE VALUES(40859,'Business Unit Folder',40858);
INSERT INTO ORGNODE VALUES(40858,'UNITED STATES',35375);
INSERT INTO ORGNODE VALUES(35375,'AMRS',16399);
INSERT INTO ORGNODE VALUES(16399,'Acme Corp.',NULL);
commit;
Above is table DDL & sample data DML.
SQL> SELECT SYS_CONNECT_BY_PATH (description,'/') || '/' scbp
2 FROM orgnode
3 where parentid is null
4 start with orgnodeid = 41801
5 CONNECT BY PRIOR PARENTID=ORGNODEID
6 /
SCBP
--------------------------------------------------------------------------------
/081350/157CFO/CFOORG/Business Unit Folder/UNITED STATES/AMRS/Acme Corp./
Above is what I've gotten to execute. I've tried many others; none of which worked.
Below is what the customer actually desires.
/Acme Corp./AMRS/UNITED STATES/Business Unit Folder/CFOORG/157CFO/081350/
I could easily get the desired results by writing a simple recursive (ascent) function in PL/SQL.
However due to application limitation the requirement is to the solution must be SQL only.
If it can be accomplished without using the CONNECT BY clause, extra points are awarded.
In the actual application, the "41801" in SQL above will actually become a bind variable.
To describe the problem in words, the input will be a leaf node ID#.
The requirement is to produce a hierarchical list from root node to leaf node; reading left to right.
The height of the hierarchy is variable.
I am going to keep poking at this, but I am hoping one of the resident SQL wizardS can bail me out.
Thanks In Advance
[Updated on: Sat, 13 September 2008 20:58] by Moderator Report message to a moderator
|
|
|
Re: need hierarchical list from root node to leaf node; reading left to right [message #347790 is a reply to message #347775] |
Sun, 14 September 2008 00:20   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
You can use a standard hierarchical query starting with the leaf node to get the rows from the leaf node to the root node, then use that as an inline view and select what you want starting with the root node, as shown below. In the sample data that you provided, the sub-query returns the whole data set, but with additional data, it should limit it to just the rows from the selected leaf node the to the root node.
SCOTT@orcl_11g> SELECT * FROM orgnode
2 /
ORGNODEID DESCRIPTION PARENTID
---------- -------------------------------- ----------
41801 081350 40955
40955 157CFO 40954
40954 CFOORG 40859
40859 Business Unit Folder 40858
40858 UNITED STATES 35375
35375 AMRS 16399
16399 Acme Corp.
7 rows selected.
SCOTT@orcl_11g> VARIABLE leaf_node NUMBER
SCOTT@orcl_11g> EXEC :leaf_node := 41801
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT MAX (SYS_CONNECT_BY_PATH (description, '/')) || '/' scbp
2 FROM (SELECT orgnodeid, parentid, description
3 FROM orgnode
4 START WITH orgnodeid = :leaf_node
5 CONNECT BY PRIOR parentid = orgnodeid)
6 START WITH parentid IS NULL -- root node
7 CONNECT BY PRIOR orgnodeid = parentid
8 /
SCBP
--------------------------------------------------------------------------------
/Acme Corp./AMRS/UNITED STATES/Business Unit Folder/CFOORG/157CFO/081350/
SCOTT@orcl_11g>
|
|
|
Re: need hierarchical list from root node to leaf node; reading left to right [message #347792 is a reply to message #347775] |
Sun, 14 September 2008 00:35   |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
For the moment I don't see a direct way; you can split the sys_connect_by_path result and rebuild it in reverse order but it is not a very neat idea:
SQL> With
2 data as (
3 select sys_connect_by_path (description,'/') || '/' scbp
4 from orgnode
5 where parentid is null
6 connect by prior parentid = orgnodeid
7 start with orgnodeid = 41801
8 ),
9 words as (
10 select xmlelement("/",
11 substr(scbp,
12 instr(scbp,'/',1,level)+1,
13 instr(scbp,'/',1,level+1)-instr(scbp,'/',1,level)-1)
14 ) word
15 from data
16 connect by level < length(scbp)-length(replace(scbp,'/',''))
17 order by level desc
18 )
19 select translate(replace(xmlagg(word),'<//>',''),'/<>','/')||'/' scbp
20 from words
21 /
SCBP
-------------------------------------------------------------------------
/Acme Corp./AMRS/UNITED STATES/Business Unit Folder/CFOORG/157CFO/081350/
1 row selected.
SQL> With
2 data as (
3 select sys_connect_by_path (description,'/') || '/' scbp
4 from orgnode
5 where parentid is null
6 connect by prior parentid = orgnodeid
7 start with orgnodeid = 41801
8 ),
9 words as (
10 select substr(scbp,
11 instr(scbp,'/',1,level)+1,
12 instr(scbp,'/',1,level+1)-instr(scbp,'/',1,level)-1
13 ) word,
14 level rn,
15 length(scbp)-length(replace(scbp,'/',''))-1 last
16 from data
17 connect by level < length(scbp)-length(replace(scbp,'/',''))
18 )
19 select sys_connect_by_path(word,'/')||'/' scbp
20 from words
21 where level = last
22 connect by prior rn = rn+1
23 start with rn = last
24 /
SCBP
-------------------------------------------------------------------------
/Acme Corp./AMRS/UNITED STATES/Business Unit Folder/CFOORG/157CFO/081350/
1 row selected.
Regards
Michel
[Updated on: Sun, 14 September 2008 00:37] Report message to a moderator
|
|
|
|
Re: need hierarchical list from root node to leaf node; reading left to right [message #347862 is a reply to message #347840] |
Sun, 14 September 2008 13:03   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
The following is more or less just thinking out loud. Perhaps you can confirm my suspicions (or not) and somebody else can take the general method and modify it. You mentioned that you prefer not to use CONNECT BY. I have also noticed some leanings toward MySQL. I am going to guess that, although you haven't said so, you are looking for something that will ultimately work across multiple platforms. That is usually the only reason for not wanting CONNECT BY. I know you said SQL only, no functions, but are you allowed to modify the data structure? And is an upgrade possible? If you were to modify your table to add left and right values, then you could use a SQL query without CONNECT BY to retrieve the values that you want in the order that you want. However, you would still need the 11g undocumented wm_concat function to get one aggregated string without CONNECT BY. I have provided an example below. I used CONNECT BY for the one-time update of the left and right columns, but you could do that differently and populate future rows automatically. Please let us know if I am on the right track here and perhaps someone else can improve upon the idea.
-- original table and data:
SCOTT@orcl_11g> SELECT * FROM orgnode
2 /
ORGNODEID DESCRIPTION PARENTID
---------- -------------------------------- ----------
41801 081350 40955
40955 157CFO 40954
40954 CFOORG 40859
40859 Business Unit Folder 40858
40858 UNITED STATES 35375
35375 AMRS 16399
16399 Acme Corp.
7 rows selected.
-- modifications:
SCOTT@orcl_11g> ALTER TABLE orgnode ADD (lft NUMBER, rgt NUMBER)
2 /
Table altered.
SCOTT@orcl_11g> UPDATE orgnode o
2 SET o.lft =
3 (SELECT i.lvl
4 FROM (SELECT LEVEL lvl, orgnodeid, parentid, description
5 FROM orgnode
6 START WITH parentid IS NULL
7 CONNECT BY PRIOR orgnodeid = parentid) i
8 WHERE o.orgnodeid = i.orgnodeid)
9 /
7 rows updated.
SCOTT@orcl_11g> UPDATE orgnode o
2 SET o.rgt = o.lft + 1 +
3 (SELECT (COUNT (*) - 1) * 2
4 FROM orgnode i
5 START WITH i.orgnodeid = o.orgnodeid
6 CONNECT BY PRIOR i.orgnodeid = i.parentid)
7 /
7 rows updated.
-- resulting modified table and data:
SCOTT@orcl_11g> SELECT * FROM orgnode
2 /
ORGNODEID DESCRIPTION PARENTID LFT RGT
---------- -------------------------------- ---------- ---------- ----------
41801 081350 40955 7 8
40955 157CFO 40954 6 9
40954 CFOORG 40859 5 10
40859 Business Unit Folder 40858 4 11
40858 UNITED STATES 35375 3 12
35375 AMRS 16399 2 13
16399 Acme Corp. 1 14
7 rows selected.
-- query:
SCOTT@orcl_11g> VARIABLE leaf_node NUMBER
SCOTT@orcl_11g> EXEC :leaf_node := 41801
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT '/' || REPLACE (WM_CONCAT (description), ',', '/') || '/' AS hierarchical_list
2 FROM (SELECT parent.description
3 FROM orgnode child,
4 orgnode parent
5 WHERE child.lft BETWEEN parent.lft AND parent.rgt
6 AND child.orgnodeid = :leaf_node
7 ORDER BY parent.lft)
8 /
HIERARCHICAL_LIST
--------------------------------------------------------------------------------
/Acme Corp./AMRS/UNITED STATES/Business Unit Folder/CFOORG/157CFO/081350/
SCOTT@orcl_11g>
|
|
|
Re: need hierarchical list from root node to leaf node; reading left to right [message #347869 is a reply to message #347775] |
Sun, 14 September 2008 13:57   |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
> you are looking for something that will ultimately work across multiple platforms.
Yes, such a capability would be icing on the cake.
The architect behind this product does not like (PL/SQL) functions because they too are not portable.
The immediate requirement is for something to work on Oracle.
WRT changing the table structure, I doubt I could sell this.
However, adding a GTT which contains LEFT & RIGHT might be viable.
At this time this requirement is for single customer reporting request.
As such it does not need to be all efficient.
Upgrading to V11 is out of the question due to wider politcal considerations.
I am wondering if wm_concat could be reverse engineered & backported to V10.2.0.4.
Thanks for your efforts.
[Updated on: Sun, 14 September 2008 13:59] by Moderator Report message to a moderator
|
|
|
|
Goto Forum:
Current Time: Mon Feb 10 11:48:29 CST 2025
|