Home » SQL & PL/SQL » SQL & PL/SQL » Complex query on hierarchy  () 1 Vote
Complex query on hierarchy [message #188193] Thu, 17 August 2006 07:12 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have a table which implements Hierarchy:

Hierarchy H1 has 1 tree:

A is parent of B1, B2
B1 is parent of C1, C2, C3
C2 is parent of D1, D2
C3 is parent of D2


(So D2 has 2 parents: C2 and C3)

Hierarchy H2 has 2 trees:

X is parent of Y1, Y2
Y2 is parent of Z1, Z2 and Z3

U is parent of V1, V2
V1 is parent of W



Here is the script to generate the table and the records:

create table Sample_Emp
(employee  varchar2(10),
 emp_name  varchar2(10));

insert into Sample_Emp values ('EMP1','A');
insert into Sample_Emp values ('EMP2','B1');
insert into Sample_Emp values ('EMP3','B2');
insert into Sample_Emp values ('EMP4','C1');
insert into Sample_Emp values ('EMP5','C2');
insert into Sample_Emp values ('EMP6','C3');
insert into Sample_Emp values ('EMP7','D1');
insert into Sample_Emp values ('EMP8','D2');
insert into Sample_Emp values ('EMP9','X');
insert into Sample_Emp values ('EMP10','Y1');
insert into Sample_Emp values ('EMP11','Y2');
insert into Sample_Emp values ('EMP12','Z1');
insert into Sample_Emp values ('EMP13','Z2');
insert into Sample_Emp values ('EMP14','Z3');
insert into Sample_Emp values ('EMP15','U');
insert into Sample_Emp values ('EMP16','V1');
insert into Sample_Emp values ('EMP17','V2');
insert into Sample_Emp values ('EMP18','W');

create table Sample_Hierarchy 
(hierarchy varchar2(10),
 employee  varchar2(10),
 manager   varchar2(10));

-- Hierachy H1, tree 1 
insert into Sample_Hierarchy values ('H1', 'EMP1','0'); -- A  is under none
insert into Sample_Hierarchy values ('H1', 'EMP2','EMP1'); -- B1 is under A
insert into Sample_Hierarchy values ('H1', 'EMP3','EMP1'); -- B2 is under A
insert into Sample_Hierarchy values ('H1', 'EMP4','EMP2'); -- C1 is under B1
insert into Sample_Hierarchy values ('H1', 'EMP5','EMP2'); -- C2 is under B1
insert into Sample_Hierarchy values ('H1', 'EMP6','EMP2'); -- C3 is under B1
insert into Sample_Hierarchy values ('H1', 'EMP7','EMP5'); -- D1 is under C2
insert into Sample_Hierarchy values ('H1', 'EMP8','EMP5'); -- D2 is under C2
insert into Sample_Hierarchy values ('H1', 'EMP8','EMP6'); -- D2 is under C3
-- Hierachy H2, tree 1
insert into Sample_Hierarchy values ('H2', 'EMP9','0');  -- X1 is under none
insert into Sample_Hierarchy values ('H2', 'EMP10','EMP9');  -- Y1 is under X
insert into Sample_Hierarchy values ('H2', 'EMP11','EMP9');  -- Y2 is under X
insert into Sample_Hierarchy values ('H2', 'EMP12','EMP11'); -- Z1 is under Y2
insert into Sample_Hierarchy values ('H2', 'EMP13','EMP11'); -- Z2 is under Y2
insert into Sample_Hierarchy values ('H2', 'EMP14','EMP11'); -- Z3 is under Y2
-- Hierachy H2, tree 2
insert into Sample_Hierarchy values ('H2', 'EMP15','0'); -- U is under none
insert into Sample_Hierarchy values ('H2', 'EMP16','EMP15'); -- V1 is under U
insert into Sample_Hierarchy values ('H2', 'EMP17','EMP15'); -- V2 is under U
insert into Sample_Hierarchy values ('H2', 'EMP18','EMP17'); -- W  is under V2


I would like a query that accepts the Hierarchy (say H1) and returns:

Hierarchy  Employee  EmpName  Level  NumDirectChild  Path
---------  --------  -------  -----  --------------  ------------------------
H1         EMP1      A        0      2               /A
H1         EMP2      B1       1      0               /A/B1
H1         EMP3      B2       1      3               /A/B2
H1         EMP4      C1       2      0               /A/B1/C1
H1         EMP5      C2       2      2               /A/B1/C2
H1         EMP6      C3       2      1               /A/B1/C3
H1         EMP7      D1       3      0               /A/B1/C2/D1
H1         EMP8      D2       3      0               /A/B1/C2/D2  
H1         EMP8      D2       3      0               /A/B1/C3/D2  


If hierarchy is H2, it should return:

Hierarchy  Employee  EmpName  Level  NumDirectChild  Path
---------  --------  -------  -----  --------------  ------------------------
H2         EMP9      X        0      2               /X
H2         EMP10     Y1       1      0               /X/Y1
H2         EMP11     Y2       1      3               /X/Y2
H2         EMP12     Z1       2      0               /X/Y2/Z1
H2         EMP13     Z2       2      0               /X/Y2/Z2
H2         EMP14     Z3       2      0               /X/Y2/Z3
H2         EMP15     U        0      2               /U
H2         EMP16     V1       1      0               /U/V1 
H2         EMP17     V2       1      1               /U/V2
H2         EMP18     W        2      0               /U/V2/W

I was playing with some analytical functions and SYS_CONNECT_BY_PATH but I still cannot figure out the correct query.. Please help..

[Updated on: Thu, 17 August 2006 07:26]

Report message to a moderator

Re: Complex query on hierarchy [message #188207 is a reply to message #188193] Thu, 17 August 2006 07:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Thank you for the excellent sample data. I gave you 4 stars for the exemplary opening post!

This might be easier than you think (unless I'm missing something).

My script:
create table Sample_Emp
(employee  varchar2(10),
 emp_name  varchar2(10));

insert into Sample_Emp values ('EMP1','A');
insert into Sample_Emp values ('EMP2','B1');
insert into Sample_Emp values ('EMP3','B2');
insert into Sample_Emp values ('EMP4','C1');
insert into Sample_Emp values ('EMP5','C2');
insert into Sample_Emp values ('EMP6','C3');
insert into Sample_Emp values ('EMP7','D1');
insert into Sample_Emp values ('EMP8','D2');
insert into Sample_Emp values ('EMP9','X');
insert into Sample_Emp values ('EMP10','Y1');
insert into Sample_Emp values ('EMP11','Y2');
insert into Sample_Emp values ('EMP12','Z1');
insert into Sample_Emp values ('EMP13','Z2');
insert into Sample_Emp values ('EMP14','Z3');
insert into Sample_Emp values ('EMP15','U');
insert into Sample_Emp values ('EMP16','V1');
insert into Sample_Emp values ('EMP17','V2');
insert into Sample_Emp values ('EMP18','W');

create table Sample_Hierarchy 
(hierarchy varchar2(10),
 employee  varchar2(10),
 manager   varchar2(10));

-- Hierachy H1, tree 1 
insert into Sample_Hierarchy values ('H1', 'EMP1','0');
insert into Sample_Hierarchy values ('H1', 'EMP2','EMP1');
insert into Sample_Hierarchy values ('H1', 'EMP3','EMP1');
insert into Sample_Hierarchy values ('H1', 'EMP4','EMP2');
insert into Sample_Hierarchy values ('H1', 'EMP5','EMP2');
insert into Sample_Hierarchy values ('H1', 'EMP6','EMP2');
insert into Sample_Hierarchy values ('H1', 'EMP7','EMP5');
insert into Sample_Hierarchy values ('H1', 'EMP8','EMP5');
insert into Sample_Hierarchy values ('H1', 'EMP8','EMP6');
-- Hierachy H2, tree 1
insert into Sample_Hierarchy values ('H2', 'EMP9','0');
insert into Sample_Hierarchy values ('H2', 'EMP10','EMP9');
insert into Sample_Hierarchy values ('H2', 'EMP11','EMP9');
insert into Sample_Hierarchy values ('H2', 'EMP12','EMP11');
insert into Sample_Hierarchy values ('H2', 'EMP13','EMP11');
insert into Sample_Hierarchy values ('H2', 'EMP14','EMP11');
-- Hierachy H2, tree 2
insert into Sample_Hierarchy values ('H2', 'EMP15','0');
insert into Sample_Hierarchy values ('H2', 'EMP16','EMP15');
insert into Sample_Hierarchy values ('H2', 'EMP17','EMP15');
insert into Sample_Hierarchy values ('H2', 'EMP18','EMP17');

commit;

col path format a30
SELECT h.hierarchy
     , h.employee
     , e.emp_name empname 
     , level
     , sys_connect_by_path(e.emp_name,'/') path
FROM   sample_hierarchy h
   ,   sample_emp       e     
WHERE  h.hierarchy = 'H1'
AND    h.employee = e.employee
CONNECT BY prior h.employee = h.manager
START WITH       h.manager = '0'
ORDER BY TO_NUMBER(SUBSTR(h.employee,instr(h.employee,'EMP')+3))
/

SELECT h.hierarchy
     , h.employee
     , e.emp_name empname 
     , level
     , sys_connect_by_path(e.emp_name,'/') path
FROM   sample_hierarchy h
   ,   sample_emp       e     
WHERE  h.hierarchy = 'H2'
AND    h.employee = e.employee
CONNECT BY prior h.employee = h.manager
START WITH       h.manager = '0'
ORDER BY TO_NUMBER(SUBSTR(h.employee,instr(h.employee,'EMP')+3))
/

DROP TABLE sample_hierarchy
/
DROP TABLE sample_emp
/


The run:
SQL> @orafaq

Table created.


1 row created.
...
<snip>
...
Commit complete.


HIERARCHY  EMPLOYEE   EMPNAME         LEVEL PATH
---------- ---------- ---------- ---------- ------------------------------
H1         EMP1       A                   1 /A
H1         EMP2       B1                  2 /A/B1
H1         EMP3       B2                  2 /A/B2
H1         EMP4       C1                  3 /A/B1/C1
H1         EMP5       C2                  3 /A/B1/C2
H1         EMP6       C3                  3 /A/B1/C3
H1         EMP7       D1                  4 /A/B1/C2/D1
H1         EMP8       D2                  4 /A/B1/C2/D2
H1         EMP8       D2                  4 /A/B1/C3/D2

9 rows selected.


HIERARCHY  EMPLOYEE   EMPNAME         LEVEL PATH
---------- ---------- ---------- ---------- ------------------------------
H2         EMP9       X                   1 /X
H2         EMP10      Y1                  2 /X/Y1
H2         EMP11      Y2                  2 /X/Y2
H2         EMP12      Z1                  3 /X/Y2/Z1
H2         EMP13      Z2                  3 /X/Y2/Z2
H2         EMP14      Z3                  3 /X/Y2/Z3
H2         EMP15      U                   1 /U
H2         EMP16      V1                  2 /U/V1
H2         EMP17      V2                  2 /U/V2
H2         EMP18      W                   3 /U/V2/W

10 rows selected.


Table dropped.


Table dropped.

SQL>


MHE
Re: Complex query on hierarchy [message #188220 is a reply to message #188207] Thu, 17 August 2006 08:25 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks, Maaher. I would be more happy to see NumDirectChild, too..
Re: Complex query on hierarchy [message #188223 is a reply to message #188220] Thu, 17 August 2006 08:26 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ah, sorry I forgot that. Give me a sec.

[EDIT]
Quick and dirty:
SELECT h.hierarchy
     , h.employee
     , e.emp_name empname 
     , level
     , ( select count(*) from sample_hierarchy where manager = h.employee) numdriectchild
     , sys_connect_by_path(e.emp_name,'/') path
FROM   sample_hierarchy h
   ,   sample_emp       e     
WHERE  h.hierarchy = 'H1'
AND    h.employee = e.employee
CONNECT BY prior h.employee = h.manager
START WITH       h.manager = '0'
ORDER BY TO_NUMBER(SUBSTR(h.employee,instr(h.employee,'EMP')+3))
/


MHE

[Updated on: Thu, 17 August 2006 08:32]

Report message to a moderator

icon7.gif  Re: Complex query on hierarchy [message #188240 is a reply to message #188223] Thu, 17 August 2006 09:08 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
thank you.. i did the same Smile
Previous Topic: how to upload excel file using pl/sql
Next Topic: How to find out whether an object is function/procedure under a package using oracle dictionery ?
Goto Forum:
  


Current Time: Sun Dec 04 14:22:27 CST 2016

Total time taken to generate the page: 0.08371 seconds