Sunday, 29 March 2009

Sum to Parent Nodes in Hierarchy Queries: CONNECT_BY_ROOT

In one of our Applicaitons, the table DEPT contains a self referncing join (Pigs Ear) as it models our organsational department hierarchy.

For example:

CREATE TABLE DEPT
(
DEPT_ID NUMBER NOT NULL
,PARENT_ID NUMBER
,DEPT_NAME VARCHAR2 (100) NOT NULL
,EMPLOYEES NUMBER NOT NULL
,CONSTRAINT DEPT_PK PRIMARY KEY (DEPT_ID)
,CONSTRAINT DEPT_FK01 FOREIGN KEY (PARENT_ID)
REFERENCES DEPT (DEPT_ID)
);

INSERT INTO DEPT VALUES (1,NULL,'IT', 100);
INSERT INTO DEPT VALUES (2,1,'DEVELOPMENT', 12);
INSERT INTO DEPT VALUES (3,1,'SUPPORT', 15);
INSERT INTO DEPT VALUES (4,1,'TEST', 25);

INSERT INTO DEPT VALUES (5,2,'PL/SQL', 2);
INSERT INTO DEPT VALUES (6,2,'Java', 1);
INSERT INTO DEPT VALUES (7,2,'SQL', 11);
INSERT INTO DEPT VALUES (8,2,'C++', 3);

INSERT INTO DEPT VALUES (9,4,'Functional', 3);
INSERT INTO DEPT VALUES (10,4,'Non Functional', 5);

COMMIT;
A quick tree walk using CONNECT BY PRIOR shows you the Parent / Child relationships between all departments and the number of employees in each department:
SELECT rpad( ' ', 1*level, ' ' ) || dept_name dept_name
,employees
FROM dept
START WITH parent_id is null
CONNECT BY PRIOR dept_id = parent_id;

DEPT_NAME EMPLOYEES
-------------------- ----------
IT 100
DEVELOPMENT 12
PL/SQL 2
Java 1
SQL 11
C++ 3
SUPPORT 15
TEST 25
Functional 3
Non Functional 5

We had a requirment to work out the total number of employees at each parent level in the Organisational Hierarchy. For example, the report sum up all employees in the parent node and all its children.

Taking the department TEST as an example, the report should sum the figures 25 (employees in the TEST department), 3 and 5 (employees in the Functional / Non Functional child departments) to give a figure of 33.

This can easily be achieved by using CONNECT_BY_ROOT. Straight from the Oracle Documentation:

"CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row. This operator extends the functionality of the CONNECT BY [PRIOR] condition of hierarchical queries.

Restriction on CONNECT_BY_ROOT You cannot specify this operator in the START WITH condition or the CONNECT BY condition.
"

To meet our requirement, CONNECT_BY_ROOT was utilised as follows:

select dept_name, employees, tot_employees
from (select
employees,
dept_name,
level lev,
sum(employees) over(partition by connect_by_root
(dept_id)
) tot_employees
from dept
connect by prior dept_id = parent_id)
where lev=1;

DEPT_NAME EMPLOYEES TOT_EMPLOYEES
-------------------- ---------- -------------
IT 100 177
DEVELOPMENT 12 29
SUPPORT 15 15
TEST 25 33
PL/SQL 2 2
Java 1 1
SQL 11 11
C++ 3 3
Functional 3 3
Non Functional 5 5

3 comments:

S Manju said...

Hi Duncan - I am trying to create a report utilizing the Hierarchy feature. I would like to see totals at every node but the report needs to display data in the 'select lpad(' ',7*(level-1)) ||' format.

Is it possible?

Thanks for any feedback you might have.

Regards,
S Manju

runcsmeduncs said...

Hi there.

Are you wanting to just change the output of the Department column so that it is indented or do you need to change how the values are summed?

Duncs

Anonymous said...

Hey - the summation is right. I need the report to display like in the first query but with the total column also like below

DEPT_NAME EMPLOYEES TOTAL
--------------- --------- -----
IT 10 177
DEVELOPMENT 12 29
PL/SQL 2
Java 1
SQL 11
C++ 3
SUPPORT 15 15
TEST 25 33
Functional 3
Non Functional 5

Thanks,
S Manju