12 May, 2012

Cumulative Summary in Hierarchical Query using CONNECT_BY_ROOT

Today I will explain how to write hierarchical query, then modify it for getting cumulative summary function of every child using CONNECT_BY_ROOT which is supported within hierarchical queries.

For example I will create hierarchical query for employees and their manager, then display for every manager  how many  employees whose he manages cumulatively.



#1 Build hierarchical Query
Build simple hierarchical  query to retrieve managers and their employees

SELECT LEVEL, EMPNO, LPAD (ENAME, LEVEL * 2 + 3, ' ') ENAME
FROM emp
CONNECT BY PRIOR EMPNO = MGR
START WITH MGR IS NULL;

The result data as below


#2 use CONNECT_BY_ROOT for getting cumulative counter of employees under manager
Display per every manager how many employees whose he manages cumulatively
I will use CONNECT_BY_ROOT in query


SELECT empno, ename, COUNT (CONNECT_BY_ROOT empno) -1 total_employees
FROM emp
CONNECT BY empno = PRIOR mgr
  GROUP BY empno, ename

The result data as below
 
Thanks 
Mahmoud A. El-Sayed

2 comments:

  1. Hi Mahmoud,

    Please at your convenience, explain how hierarchical query works and what are each CONNECT_BY_..... cluase means, I output is neat, what should our thought process be to understand hierarchical queries.

    Regards,
    Bimal

    ReplyDelete

How to Pass Parameters to ActionListener in ADF

In some cases, it is required to pass a value to ActionListener of ADF Button. The method that can be invoked by actionListeners has only...