Re: Recursive query help

See of this works:

select rpad('*',2*level,'*') || ename name, deptno, sal   , (select sum(e2.sal)
    from emp e2
    start with e2.empno = e1.empno
    connect by prior empno = mgr) as tot_sal, connect_by_isleaf,
  case connect_by_isleaf
    when 1 then sal
    else (select sum(e2.sal)
    from emp e2
    start with e2.empno = e1.empno
    connect by prior empno = mgr)
  end as new_tot_sal
from emp e1
start with e1.mgr is null
connect by prior empno = mgr

Query results follow:

NAME                               DEPTNO        SAL    TOT_SAL 

------------------------------ ---------- ---------- ---------- ----------------- 
 -----------


**KING                                 10       5000      29025 

0       29025


****JONES                              20       2975      10875 

0       10875


******SCOTT                            20       3000       4100 

0        4100


********ADAMS                          20       1100       1100 

1        1100


******FORD                             20       3000       3800 

0        3800


********SMITH                          20        800        800 

1         800


****BLAKE                              30       2850       9400 

0        9400


******ALLEN                            30       1600       1600 

1        1600


******WARD                             30       1250       1250 

1        1250


******MARTIN                           30       1250       1250 

1        1250


******TURNER                           30       1500       1500 

1        1500


******JAMES                            30        950        950 

1         950


****CLARK                              10       2450       3750 

0        3750


******MILLER                           10       1300       1300 

1        1300

Cheers.

<jackal_work_at_yahoo.com> wrote in message news:1167135601.293666.136270_at_42g2000cwt.googlegroups.com...

> Hi faculties,
>    I have this query and the output it gives is as follows:
>
> SQL> select rpad('*',2*level,'*') || ename name, sal,
>               (select sum(e2.sal)
>                  from emp e2
>                     start with e2.empno = e1.empno
>               connect by prior e2.mgr = e2.empno) tot_sal
>      from emp e1
>     start with e1.mgr is null
>     connect by prior empno = mgr;
>
> NAME                                  SAL    TOT_SAL
> ------------------------------ ---------- ----------
> **KING                               5000       5000
> ****JONES                            2975       7975
> ******SCOTT                          3000      10975
> ********ADAMS                        1100      12075
> ******FORD                           3000      10975
> ********SMITH                         800      11775
> ****BLAKE                            2850       7850
> ******ALLEN                          1600       9450
> ******WARD                           1250       9100
> ******MARTIN                         1250       9100
> ******TURNER                         1500       9350
> ******JAMES                           950       8800
> ****CLARK                            2450       7450
> ******MILLER                         1300       8750
>
> Here the total sum is displayed at the child level. Instead i want the
> sum of the parents and childs to be displayed at the root level and
> sublevels only. So the leaf level will show only its salary.The
> immediate parent should show the sum of its salary and the child's
> salary and so on till the root level. Can the existing be modified to
> achieve the desired results?
>
>
> Thanks in advance
> Jackal
>