How to get all the parents of a given node from a tree in single row?

[srinivasm]

HI,


I have a requirement to get the all the parent nodes till the root, of all the leaf nodes in a Tree structure in a single row instead of multiple rows.

Normally we can get the tree structure as follows for a leaf level node_id : 1023.


select node_id,node_name,node_level,parent_node_id
from tree
start with node_id=1023
connect by prior parent_node_id=node_id

This query returns all the parents of the leaf nodes, but in multiple rows as follows


1023, node_district, 4, 1022
1022, node_region, 3, 1021
1021, node_state, 2, 1020
1020, node_company, 1, 0


but my requirement is to get the node_names as follows in a single row.

node_company, node_state, node_region, node_district

How should I achieve this with a SQL query?

Thank you,
Srinivas Medukonduru

[padders]

What Oracle version? (it is critical here)

[srinivasm]

I am using Oracle 8.1.7.2.

[balajiyes]

With 8.1.7, the only option would be write a user-defined function, I guess. 9i has a ready function for it though - sys_connect_by_path.

[padders]

Go to http://revealnet.com/pipelines/plsql/archives.htm read 'Using Order By with Connect By' and get hierarchy package. Then do something like this...

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

SQL> CREATE OR REPLACE PACKAGE hierarchy
  2  IS
  3    TYPE branchtabletype IS TABLE OF VARCHAR2 (4000)
  4      INDEX BY BINARY_INTEGER;
  5  
  6    branchtable branchtabletype;
  7  
  8    FUNCTION branch (
  9      vlevel IN NUMBER,
 10      vvalue IN VARCHAR2,
 11      vdelimiter IN VARCHAR2 DEFAULT CHR (0))
 12      RETURN VARCHAR2;
 13  
 14  END hierarchy;
 15  /

Package created.

SQL> 
SQL> CREATE OR REPLACE PACKAGE BODY hierarchy
  2  IS
  3    returnvalue VARCHAR2 (4000);
  4  
  5    FUNCTION branch (
  6      vlevel IN NUMBER,
  7      vvalue IN VARCHAR2,
  8      vdelimiter IN VARCHAR2 DEFAULT CHR (0))
  9      RETURN VARCHAR2
 10    IS
 11    BEGIN
 12      branchtable (vlevel) := vvalue;
 13      returnvalue := vvalue;
 14  
 15      FOR i IN REVERSE 1 .. vlevel - 1 LOOP
 16        returnvalue := branchtable (i) || vdelimiter || returnvalue;
 17      END LOOP;
 18  
 19      RETURN returnvalue;
 20    END branch;
 21    
 22  END hierarchy;
 23  /

Package body created.

SQL> SELECT path 
  2  FROM  (SELECT path, LEAD (path) OVER ( 
  3                  ORDER BY ROWNUM) next_path 
  4         FROM  (SELECT ROWNUM row_num, 
  5                       hierarchy.branch ( 
  6                         LEVEL, ename, ', ') path 
  7                FROM   emp 
  8                START WITH mgr IS NULL 
  9                CONNECT BY PRIOR empno = mgr)) 
 10  WHERE  next_path NOT LIKE path || '%';

PATH
--------------------------------------------------------------------------------
KING, JONES, SCOTT, ADAMS
KING, JONES, FORD, SMITH
KING, BLAKE, ALLEN
KING, BLAKE, WARD
KING, BLAKE, MARTIN
KING, BLAKE, TURNER
KING, BLAKE, JAMES

7 rows selected.

SQL>

[jmodic]

SELECT MAX(node_company),
       MAX(node_state),
       MAX(node_region),
       MAX(node_district)
FROM
 (SELECT DECODE(node_level,1,node_name) node_company,
         DECODE(node_level,2,node_name) node_state,
         DECODE(node_level,3,node_name) node_region,
         DECODE(node_level,4,node_name) node_district
  FROM
   (select node_id,node_name,node_level,parent_node_id
    from tree
    start with node_id=1023
    connect by prior parent_node_id=node_id
   )
 );

The assumption here is that each leaf node you are making the report for is at exactly fourth level, ie, it allways has three parent levels.

[srinivasm]

Hi jmodic,

Your query is working fine for one leaf node, but if I give more than one leaf node in the query, its loading parents information of only one leaf.

I have tried as follows...

 
SELECT MAX(node_company),
       MAX(node_state),
       MAX(node_region),
       MAX(node_district)
FROM
 (SELECT DECODE(node_level,1,node_name) node_company,
         DECODE(node_level,2,node_name) node_state,
         DECODE(node_level,3,node_name) node_region,
         DECODE(node_level,4,node_name) node_district
  FROM
   (select node_id,node_name,node_level,parent_node_id
    from tree
    start with node_id in (1023,1080,1040) 
    connect by prior parent_node_id=node_id
   )
 );

How we can achive it for multiple leaf nodes ?

Thanks in advance

Regards,
Srinivas Medukonduru

[padders]

Following on from BOTH examples...

Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production

SQL> CREATE TABLE tree (
  2    node_id NUMBER, node_name VARCHAR2 (30),
  3    node_level NUMBER, parent_node_id NUMBER);

Table created.

SQL>   
SQL> INSERT INTO tree VALUES (1023, 'node_district_a', 4, 1022);

1 row created.

SQL> INSERT INTO tree VALUES (1022, 'node_region_a', 3, 1021);

1 row created.

SQL> INSERT INTO tree VALUES (1021, 'node_state_a', 2, 1020);

1 row created.

SQL> INSERT INTO tree VALUES (1020, 'node_company_a', 1, 0);

1 row created.

SQL> INSERT INTO tree VALUES (1080, 'node_district_b', 4, 1079);

1 row created.

SQL> INSERT INTO tree VALUES (1079, 'node_region_b', 3, 1078);

1 row created.

SQL> INSERT INTO tree VALUES (1078, 'node_state_b', 2, 1077);

1 row created.

SQL> INSERT INTO tree VALUES (1077, 'node_company_b', 1, 0);

1 row created.

SQL> SELECT   MAX (company) company, MAX (state) state, 
  2           MAX (region) region, MAX (district) district
  3  FROM    (SELECT b.*, SUM (DECODE (lvl, lag_lvl + 1, 0, 1)) 
  4                    OVER (ORDER BY row_num) grp
  5           FROM  (SELECT DECODE (node_level, 1, node_name) company,
  6                         DECODE (node_level, 2, node_name) state,
  7                         DECODE (node_level, 3, node_name) region,
  8                         DECODE (node_level, 4, node_name) district,
  9                         row_num, lvl, LAG (lvl) OVER (ORDER BY row_num) lag_lvl
 10                  FROM  (SELECT node_id, node_name, node_level,
 11                                parent_node_id, ROWNUM row_num, LEVEL lvl
 12                         FROM   tree
 13                         START WITH node_id IN (1023, 1080)
 14                         CONNECT BY PRIOR parent_node_id = node_id)) b)
 15  GROUP BY grp;

COMPANY         STATE           REGION          DISTRICT
--------------- --------------- --------------- ---------------
node_company_a  node_state_a    node_region_a   node_district_a
node_company_b  node_state_b    node_region_b   node_district_b

SQL> SELECT path 
  2  FROM  (SELECT path, LEAD (path) OVER ( 
  3                  ORDER BY ROWNUM) next_path 
  4         FROM  (SELECT ROWNUM row_num, 
  5                       hierarchy.branch ( 
  6                         LEVEL, node_name, ', ') path 
  7                FROM   tree
  8                START WITH node_id IN (1023, 1080)
  9                CONNECT BY PRIOR parent_node_id = node_id)) 
 10  WHERE  next_path NOT LIKE path || '%'  OR next_path IS NULL;

PATH
----------------------------------------------------------------------------------------------------
node_district_a, node_region_a, node_state_a, node_company_a
node_district_b, node_region_b, node_state_b, node_company_b

SQL>

[jmodic]

Originally posted by srinivasm
How we can achive it for multiple leaf nodes ?

Look at padders's suggestions. In case for some reason you can't (or won't) use analytical functions, you can still use my method, only slightly modified:

SELECT MAX(node_company),
       MAX(node_state),
       MAX(node_region),
       MAX(node_district)
FROM
 (SELECT DECODE(node_level,1,node_name) node_company,
         DECODE(node_level,2,node_name) node_state,
         DECODE(node_level,3,node_name) node_region,
         DECODE(node_level,4,node_name) node_district,
         CEIL(ROWNUM/4) bucket
  FROM
   (SELECT node_id,node_name,node_level,parent_node_id
    FROM tree
    START WITH node_id IN (1023,1080)
    CONNECT BY PRIOR parent_node_id=node_id
   )
 )
GROUP BY bucket;

[srinivasm]

Hi JModic,

Thank you for your query.

When I try the padder's query(which has the analytical functions),
the performance is very slow for large number of nodes(almost 1967 nodes) in the "in clause".
Then I have tried your query without analytical functions.
But I have one problem your query, that the nodes in the "IN clause" are not always level 4 nodes,
those may be any level from 4 to 1.

If the node in the "IN clause" is 4 then it returns 4,3,2,1.
If the node in the "IN clause" is 3 then it returns 3,2,1.
If the node in the "IN clause" is 2 then it returns 2,1.
If the node in the "IN clause" is 1 then it returns 1.

For the above scenario's CEIL(ROWNUM/4) doesn't work to group the results.


How we can acheive this without using the analytical functions?


Thanks in advance.


regards,
Srinivas Medukonduru