troubleshooting Question

Connect By Prior

Avatar of systemsautomation
systemsautomationFlag for Pakistan asked on
DatabasesOracle DatabaseSQL
2 Comments1 Solution1140 ViewsLast Modified:
Hi

My database is Oracle XE

I am trying to build a hierarchal query which calculates sum at every level. I found a blog on this at:

http://djmein.blogspot.com/2009/03/sum-to-parent-nodes-in-hierarchy.html
 
I followed this and almost worked.

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;


The above query gives the desired results.

But if I use connect_by_root with two columns the output is quite wrong. Following the same example even if I just make a copy of this the column 'tot_employees' the output of first column tot_employees become wrong


select dept_name, employees, tot_employees, tot_employees2
from (select
           employees,
           dept_name,
           level lev,
           sum(employees) over(partition by connect_by_root
                                           (dept_id)
                               ) tot_employees,
           sum(employees) over(partition by connect_by_root
                                           (dept_id)
                               ) tot_employees2      

from dept
    Connect By Prior Dept_Id = Parent_Id)
    Where Lev=1;

I have added column tot_employees2 in the above query by copying tot_employees column.

Please help me in fixing the issue.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 2 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros