How to get all the parents of a given node from a tree in single row?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

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

  1. #1
    Join Date
    Dec 2000
    Posts
    95

    Question 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

  2. #2
    Join Date
    Jan 2004
    Posts
    162
    What Oracle version? (it is critical here)

  3. #3
    Join Date
    Dec 2000
    Posts
    95

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

    I am using Oracle 8.1.7.2.

  4. #4
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    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.
    Cheers!
    OraKid.

  5. #5
    Join Date
    Jan 2004
    Posts
    162
    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>

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

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

    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.
    Last edited by jmodic; 09-14-2004 at 12:42 PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Dec 2000
    Posts
    95
    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

  8. #8
    Join Date
    Jan 2004
    Posts
    162
    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>

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    Dec 2000
    Posts
    95

    How we can achive it for multiple nodes without using the analytical functions?

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width