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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #347840 is a reply to message #347775] Sun, 14 September 2008 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>For the moment I don't see a direct way; you can split the sys_connect_by_path result
It is NOT a requirement that SYS_CONNECT_BY_PATH be used at all.
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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

Re: need hierarchical list from root node to leaf node; reading left to right [message #347876 is a reply to message #347869] Sun, 14 September 2008 15:09 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Having noticed similar trends in the past where code that Tom Kyte writes ends up as part of a future Oracle version, I suspect that wm_concat is some variation of Tom Kyte's well-known stragg function. You could certainly substitute that, but I thought you didn't want any functions, just sql.
Previous Topic: Insert compound foreign key after table creation
Next Topic: insert into clustered table
Goto Forum:
  


Current Time: Mon Feb 10 11:48:29 CST 2025