Hierarchical Query: Roll-up leaf nodes to ancestors.

June 26, 2015 at 11:54 am | Posted in Oracle Developement | Leave a comment

This query may be more applicable to a bill of materials type of query. I’ve added a new column to the EMP table (value number). Then I only populated a value for this column for the leaf nodes, i.e. the employees lowest in the hierarchy (ADAMS, SMITH, ALLEN, WARD, MARTIN, TURNER, JAMES, MILLER).

This query the sums up these leaf node values to their manager, and to the manager’s manager.


with totals as (
  select empno
        ,ename
        ,mgr
        ,value
        ,level lev
        ,rownum rn
        ,sum(nvl(value, 0))
           over(
             partition by connect_by_root(empno)
             ) tot_val
   from emp t
connect by prior empno=mgr
)

, lev1 as (
  select  empno
         ,mgr
         ,rn
         ,ename
         ,value
         ,tot_val
    from totals
   where lev = 1
)

select lpad(' ', 2*level, ' ')
       ||ename name
       ,value
       ,tot_val
  from lev1
connect by prior empno=mgr
start with mgr is null 

Hierarchy Rollup

Advertisements

Leave a Comment »

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.
Entries and comments feeds.

%d bloggers like this: