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

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...