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

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

What Oracle version? (it is critical here)

I am using Oracle 8.1.7.2.

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.

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...

Code:

---

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>

---

Code:

---

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.

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...

Code:

---


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

Following on from BOTH examples...

Code:

---

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>

---

Quote:

---

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:

Code:

---

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;

---

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

Hi All,

Can't we achive retrive all the parents of group of nodes from a tree
in single row, without using the analytical functions? For some reasons the query with the analytical functions is taking more time to execute. Please read the following message. Thanks for any help.
------------------------------------------------------------------
Thank you JMODIC 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