Home » SQL & PL/SQL » SQL & PL/SQL » Looping in SQL - Hirerarchical Tree (Oracle 10g)
Looping in SQL - Hirerarchical Tree [message #379384] Tue, 06 January 2009 04:51 Go to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Hi All
I have data as below:

 PROG_CD  PARENT_ID ROLE_NAM NAME
-------- ---------- -------- --------------------------------
       1          0        1 System Administration
       2          0        1 Material Management System
       3          0        1 Sales & Distribution
       4          0        1 Finance System
       5          1        2 Company Master
       6          1        1 Country Master
       7          2        1 Masters
       9          1        1 Document Generation
      10          1        1 Role Master
      11          1        1 Division Master
      12          1        1 Location Master
      13          1        1 Department Master
      16          1        2 Employee Master
      29         52        2 Delivery Terms master
      52          7        1 Others



Now for all rows where ROLE=2, I wish to find all its parents.
I mean is it possible to have a single query which can help me populate a Hierarchical Tree as below:

System Administration
  Company Master
  Employee Master
Material Management System
  Masters
     Others
       Delivery Terms master 



Thanks
Rushi


Re: Looping in SQL - Hirerarchical Tree [message #379386 is a reply to message #379384] Tue, 06 January 2009 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at CONNECT BY

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Looping in SQL - Hirerarchical Tree [message #379396 is a reply to message #379386] Tue, 06 January 2009 05:35 Go to previous messageGo to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Hi Michel

Please find the script for Create and Insert

CREATE TABLE TEST_PROG_TREE
(
PROG_CD    NUMBER(2),
PARENT_CD  NUMBER(2),
ROLE_CD    NUMBER(2),
PROG_NAME  VARCHAR2(100)
)
/

INSERT INTO TEST_PROG_TREE VALUES (1,0,1,'System Administration')
/
INSERT INTO TEST_PROG_TREE VALUES (2,0,1,'Material Management System')
/
INSERT INTO TEST_PROG_TREE VALUES (3,0,1,'Sales - Distribution')
/
INSERT INTO TEST_PROG_TREE VALUES (4,0,1,'Finance System')
/
INSERT INTO TEST_PROG_TREE VALUES (5,1,2,'Company Master')
/
INSERT INTO TEST_PROG_TREE VALUES (6,1,1,'Country Master')
/
INSERT INTO TEST_PROG_TREE VALUES (7,2,1,'Masters')
/
INSERT INTO TEST_PROG_TREE VALUES (11,1,1,'Division Master')
/
INSERT INTO TEST_PROG_TREE VALUES (12,1,1,'Location Master')
/
INSERT INTO TEST_PROG_TREE VALUES (13,1,1,'Department Master')
/
INSERT INTO TEST_PROG_TREE VALUES (9,1,1,'Document Generation')
/
INSERT INTO TEST_PROG_TREE VALUES (10,1,1,'Role Master')
/
INSERT INTO TEST_PROG_TREE VALUES (16,1,2,'Employee Master')
/
INSERT INTO TEST_PROG_TREE VALUES (29,52,2,'Delivery Terms master')
/
INSERT INTO TEST_PROG_TREE VALUES (52,7,1,'Others')
/


COMMIT
/



I wish to Populate tree for Role_Cd = 2 and all its parents and their parents. The Parents will not have the Role_Cd = 2.
Re: Looping in SQL - Hirerarchical Tree [message #379413 is a reply to message #379396] Tue, 06 January 2009 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your requirements are not clear.
Why there is not "Document Generation" under "System Administration" in your result for instance? It has not a parent with role=2!

Regards
Michel
Re: Looping in SQL - Hirerarchical Tree [message #379441 is a reply to message #379413] Tue, 06 January 2009 08:16 Go to previous messageGo to next message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
The requirement is as follows:

I have Program Master, Role master and ROLE-PROGRAM-LINK as 3 tables. Any ROLE who logs gets access to all the Programs as defined in the ROLE-PROGRAM-LINK table.

For the same I have to Populate a tree w.r.t the Role. The Items(Leaf items) in the Tree are Links to call the respective Programs.
Now for Role=2 it does not have right to the Program "Document Generator".

One way for me is, while creating ROLE-PROGRAM-LINK I will have to insert entries for all the Parents
e.g for Delivery term Master
I have to make entries for all its Parents in the Tree.

Or else I have to come out with a query whcih finds all its Parents to TOP level.

Rgds
Rushi
Re: Looping in SQL - Hirerarchical Tree [message #379442 is a reply to message #379441] Tue, 06 January 2009 08:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does "w.r.t" mean?

Regards
Michel
Re: Looping in SQL - Hirerarchical Tree [message #379443 is a reply to message #379442] Tue, 06 January 2009 08:23 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
W.r.t. is an acceptable English language acronym (not IM speak) for "with regards to".
Re: Looping in SQL - Hirerarchical Tree [message #379444 is a reply to message #379443] Tue, 06 January 2009 08:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Will have to put it somewhere in my memory. Smile

Regards
Michel
Re: Looping in SQL - Hirerarchical Tree [message #379448 is a reply to message #379396] Tue, 06 January 2009 09:13 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
Hope You find all needed informations for Your query in the example SELECT:
SELECT  tpt.*, 
        LPAD(' ', 2 * LEVEL - 1) || prog_name pname,
        SYS_CONNECT_BY_PATH(prog_name, '/')   epfad,
	CONNECT_BY_ROOT prog_name             proot
      FROM test_prog_tree tpt
      CONNECT BY parent_cd = PRIOR prog_cd


PROG_CD	PARENT_CD ROLE_CD	PROG_NAME	PNAME	EPFAD	PROOT
1	0	  1	System Administration	 System Administration	/System Administration	System Administration
5	1	  2	Company Master	   Company Master	/System Administration/Company Master	System Administration
13	1	  1	Department Master	   Department Master	/System Administration/Department Master	System Administration
...
10	1	  1	Role Master	   Role Master	/System Administration/Role Master	System Administration
16	1	  2	Employee Master	   Employee Master	/System Administration/Employee Master	System Administration

[Updated on: Tue, 06 January 2009 09:15]

Report message to a moderator

Re: Looping in SQL - Hirerarchical Tree [message #379531 is a reply to message #379396] Tue, 06 January 2009 23:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
All you need is the final query below, but I have provided a step by step breakdown, so that you can understand how it works.

-- test data:
SCOTT@orcl_11g> COLUMN prog_name FORMAT A30
SCOTT@orcl_11g> SELECT * FROM test_prog_tree
  2  /

   PROG_CD  PARENT_CD    ROLE_CD PROG_NAME
---------- ---------- ---------- ------------------------------
         1          0          1 System Administration
         2          0          1 Material Management System
         3          0          1 Sales - Distribution
         4          0          1 Finance System
         5          1          2 Company Master
         6          1          1 Country Master
         7          2          1 Masters
        11          1          1 Division Master
        12          1          1 Location Master
        13          1          1 Department Master
         9          1          1 Document Generation
        10          1          1 Role Master
        16          1          2 Employee Master
        29         52          2 Delivery Terms master
        52          7          1 Others

15 rows selected.


-- select the data by walking the hierarchical tree in reverse:
SCOTT@orcl_11g> SELECT DISTINCT test_prog_tree.*, LEVEL
  2  FROM   test_prog_tree
  3  START  WITH role_cd = 2
  4  CONNECT BY PRIOR parent_cd = prog_cd
  5  /

   PROG_CD  PARENT_CD    ROLE_CD PROG_NAME                           LEVEL
---------- ---------- ---------- ------------------------------ ----------
         1          0          1 System Administration                   2
         7          2          1 Masters                                 3
        29         52          2 Delivery Terms master                   1
         5          1          2 Company Master                          1
        16          1          2 Employee Master                         1
        52          7          1 Others                                  2
         2          0          1 Material Management System              4

7 rows selected.


-- using the above as a subquery, put the data in forward order:
SCOTT@orcl_11g> SELECT	t.*, LEVEL
  2  FROM    (SELECT  DISTINCT test_prog_tree.*
  3  	      FROM    test_prog_tree
  4  	      START   WITH role_cd = 2
  5  	      CONNECT BY PRIOR parent_cd = prog_cd) t
  6  START   WITH parent_cd = 0
  7  CONNECT BY PRIOR prog_cd = parent_cd
  8  /

   PROG_CD  PARENT_CD    ROLE_CD PROG_NAME                           LEVEL
---------- ---------- ---------- ------------------------------ ----------
         1          0          1 System Administration                   1
         5          1          2 Company Master                          2
        16          1          2 Employee Master                         2
         2          0          1 Material Management System              1
         7          2          1 Masters                                 2
        52          7          1 Others                                  3
        29         52          2 Delivery Terms master                   4

7 rows selected.


-- apply some formatting:
SCOTT@orcl_11g> SELECT	LPAD (' ', 2 * LEVEL - 1) || prog_name
  2  FROM    (SELECT  DISTINCT test_prog_tree.*
  3  	      FROM    test_prog_tree
  4  	      START   WITH role_cd = 2
  5  	      CONNECT BY PRIOR parent_cd = prog_cd)
  6  START   WITH parent_cd = 0
  7  CONNECT BY PRIOR prog_cd = parent_cd
  8  /

LPAD('',2*LEVEL-1)||PROG_NAME
--------------------------------------------------------------------------------
 System Administration
   Company Master
   Employee Master
 Material Management System
   Masters
     Others
       Delivery Terms master

7 rows selected.

SCOTT@orcl_11g>

Re: Looping in SQL - Hirerarchical Tree [message #380129 is a reply to message #379531] Fri, 09 January 2009 04:07 Go to previous message
rushimails
Messages: 24
Registered: November 2008
Location: Mumbai
Junior Member
Hi Barbara Boehmer

Thank you so much. It perfectly solves my problem.
That is exactly what I was looking in for.

Thks & Regards
Atul P
Previous Topic: Stripping a field in the format xx.xx.xx. into seperate fields
Next Topic: to_number problem
Goto Forum:
  


Current Time: Sat Dec 10 18:27:18 CST 2016

Total time taken to generate the page: 0.10441 seconds