|
Replies:
8
-
Pages:
1
-
Last Post:
Jul 23, 2007 5:12 AM
Last Post By: Alessandro Rossi
|
|
|
Posts:
8
Registered:
05/30/07
|
|
|
|
Sum upto Highest parent in Hierarchy
Posted:
Jul 20, 2007 4:19 AM
|
|
|
Hello,
Pls find my requirement as below.
I have following data:
CLIENT_ID IMMEDIATE_ID REVENUE
40 25
30 40 25
1 4 10
4 8 10
8 10
5 10 10
10 10
I need the output as follows:
CLIENT_ID IMMEDIATE_ID REVENUE
40 50
30 40 25
1 4 10
4 8 20
8 30
5 10 10
10 20
The concept as follows:
40 has no parent which has qty 25
and 40 is parent of 30. So the 40 should get the sum of its own qty and its child qty. Similarly every parent should have sum of all its child's qty.
Cheers
Ram |
|
|
Posts:
986
Registered:
04/14/07
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 20, 2007 4:41 AM
in response to: user577333
|
|
|
SQL> select lpad(' ', 2 * (level - 1), ' ') || ename, t.*
2 from scott.emp t
3 connect by prior empno = mgr
4 start with mgr is null
5 /
LPAD('',2*(LEVEL-1),'')||ENAME EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------------------------------------------------------------------------------- ----- ---------- --------- ----- ----------- --------- --------- ------
KING 7839 KING PRESIDENT 17.11.1981 5000.00 10
JONES 7566 JONES MANAGER 7839 02.04.1981 2975.00 20
SCOTT 7788 SCOTT ANALYST 7566 19.04.1987 3000.00 20
ADAMS 7876 ADAMS CLERK 7788 23.05.1987 1100.00 20
FORD 7902 FORD ANALYST 7566 03.12.1981 3000.00 20
SMITH 7369 SMITH CLERK 7902 17.12.1980 800.00 20
BLAKE 7698 BLAKE MANAGER 7839 01.05.1981 2850.00 30
ALLEN 7499 ALLEN SALESMAN 7698 20.02.1981 1600.00 300.00 30
WARD 7521 WARD SALESMAN 7698 22.02.1981 1250.00 500.00 30
MARTIN 7654 MARTIN SALESMAN 7698 28.09.1981 1250.00 1400.00 30
TURNER 7844 TURNER SALESMAN 7698 08.09.1981 1500.00 0.00 30
JAMES 7900 JAMES CLERK 7698 03.12.1981 950.00 30
CLARK 7782 CLARK MANAGER 7839 09.06.1981 2450.00 10
MILLER 7934 MILLER CLERK 7782 23.01.1982 1300.00 10
14 rows selected
SQL>
SQL> select empno, ename, sal, tot_sal from (select empno,
2 ename,
3 sal,
4 level lev,
5 sum(sal) over(partition by connect_by_root(empno)) tot_sal
6 from scott.emp t
7 connect by prior empno = mgr)
8 where lev=1;
EMPNO ENAME SAL TOT_SAL
----- ---------- --------- ----------
7369 SMITH 800.00 800
7499 ALLEN 1600.00 1600
7521 WARD 1250.00 1250
7566 JONES 2975.00 10875
7654 MARTIN 1250.00 1250
7698 BLAKE 2850.00 9400
7782 CLARK 2450.00 3750
7788 SCOTT 3000.00 4100
7839 KING 5000.00 29025
7844 TURNER 1500.00 1500
7876 ADAMS 1100.00 1100
7900 JAMES 950.00 950
7902 FORD 3000.00 3800
7934 MILLER 1300.00 1300
14 rows selected
SQL>
|
|
|
Posts:
8
Registered:
05/30/07
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 20, 2007 7:47 AM
in response to: Volder
|
|
|
|
Hi Volder,
That's great. Thanks a lot. But i hope this solution fit for 10g.I'm working on 9i.
Any solution for 9i?
Cheers
Ram
|
|
|
Posts:
986
Registered:
04/14/07
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 20, 2007 1:26 PM
in response to: user577333
|
|
|
there was a substitute for connect_by_root for 9i many times in this forum
replace(sys_connect_by_path(decode(level,1,ename),'~'),'~')
|
|
|
Posts:
940
Registered:
08/09/06
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 23, 2007 2:59 AM
in response to: user577333
|
|
|
I don't know if model is supported on 9i. If so try this.
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno,vs as salary_sum
2 from (
3 select level as lev,empno,ename,job,mgr,hiredate,sal,comm,deptno,sal as vsal/*,cast(ename as varchar2(4000)) as people*/
4 from scott.emp
5 start with mgr is null
6 connect by mgr = prior empno
7 )
8 model
9 dimension by (lev,empno,mgr as m)
10 measures (sal,vsal as vs,mgr, ename,job,hiredate,comm,deptno/*,people*/)
11 rules automatic order(
12 vsany,any,any= salcv(),cv(),cv()+nvl(sum(vs)cv(lev)+1,any, cv(empno),0)
13 /*,peopleany,any,any = enamecv(),cv(),cv()||'('||salcv(),cv(),cv()||')'||nvl(
14 concat_string('+'||people)cv(lev)+1,any, cv(empno),''
15 )*/
16 )
17 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SALARY_SUM
----------
----------
----------
----------
7839 KING PRESIDENT 17-NOV-81 5000 10 29025
7566 JONES MANAGER 7839 02-APR-81 2975 20 10875
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 4100
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 1100
7902 FORD ANALYST 7566 03-DEC-81 3000 20 3800
7369 SMITH CLERK 7902 17-DEC-80 800 20 800
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 9400
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 1600
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 1250
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 1250
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 1500
7900 JAMES CLERK 7698 03-DEC-81 950 30 950
7782 CLARK MANAGER 7839 09-JUN-81 2450 10 3750
7934 MILLER CLERK 7782 23-JAN-82 1300 10 1300
14 rows selected.
SQL>
By Alessandro |
|
|
Posts:
986
Registered:
04/14/07
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 23, 2007 3:41 AM
in response to: Alessandro Rossi
|
|
|
@Alessandro
1. MODEL is available only starting from 10g
2. What for do you build hierarchy before? You're going through the hierarchy in the right order (from the leaf nodes to the parent nodes) by using AUTOMATIC RULES ORDER.
So it is not necessary to use CONNECT BY and LEVEL as one of the dimensions, IMHO:
SQL> select empno,ename,job,mgr,hiredate,sal,comm,deptno,vs as salary_sum
2 from scott.emp
3 model
4 dimension by (empno,mgr)
5 measures (sal,0 vs, ename,job,hiredate,comm,deptno)
6 rules automatic order (vs[any,any]= sal[cv(),cv()]+nvl(sum(vs)[any, cv(empno)],0))
7 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SALARY_SUM
---------- ---------- --------- ---------- ----------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17.12.1980 800 20 800
7499 ALLEN SALESMAN 7698 20.02.1981 1600 300 30 1600
7521 WARD SALESMAN 7698 22.02.1981 1250 500 30 1250
7566 JONES MANAGER 7839 02.04.1981 2975 20 10875
7654 MARTIN SALESMAN 7698 28.09.1981 1250 1400 30 1250
7698 BLAKE MANAGER 7839 01.05.1981 2850 30 9400
7782 CLARK MANAGER 7839 09.06.1981 2450 10 3750
7788 SCOTT ANALYST 7566 19.04.1987 3000 20 4100
7839 KING PRESIDENT 17.11.1981 5000 10 29025
7844 TURNER SALESMAN 7698 08.09.1981 1500 0 30 1500
7876 ADAMS CLERK 7788 23.05.1987 1100 20 1100
7900 JAMES CLERK 7698 03.12.1981 950 30 950
7902 FORD ANALYST 7566 03.12.1981 3000 20 3800
7934 MILLER CLERK 7782 23.01.1982 1300 10 1300
14 rows selected
SQL>
or do I miss smth in your logic?
3. BTW - it is very good example of using AUTOMATIC ORDER in the MODEL clause - thanks.
|
|
|
Posts:
5,305
Registered:
08/17/06
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 23, 2007 4:19 AM
in response to: Volder
|
|
|
Volder,
A slightly easier query to do the same:
SQL> select empno, ename, sal, tot_sal
2 from ( select empno,
3 ename,
4 sal,
5 level lev,
6 sum(sal) over(partition by connect_by_root(empno)) tot_sal
7 from emp t
8 connect by prior empno = mgr
9 )
10 where lev=1
11 order by empno
12 /
EMPNO ENAME SAL TOT_SAL
----------
----------
7369 SMITH 800 800
7499 ALLEN 1600 1600
7521 WARD 1250 1250
7566 JONES 2975 10875
7654 MARTIN 1250 1250
7698 BLAKE 2850 9400
7782 CLARK 2450 3750
7788 SCOTT 3000 4100
7839 KING 5000 29025
7844 TURNER 1500 1500
7876 ADAMS 1100 1100
7900 JAMES 950 950
7902 FORD 3000 3800
7934 MILLER 1300 1300
14 rijen zijn geselecteerd.
SQL> select empno
2 , ename
3 , sal
4 , sum(connect_by_root sal) tot_sal
5 from emp
6 connect by prior mgr = empno
7 group by empno
8 , ename
9 , sal
10 order by empno
11 /
EMPNO ENAME SAL TOT_SAL
----------
----------
7369 SMITH 800 800
7499 ALLEN 1600 1600
7521 WARD 1250 1250
7566 JONES 2975 10875
7654 MARTIN 1250 1250
7698 BLAKE 2850 9400
7782 CLARK 2450 3750
7788 SCOTT 3000 4100
7839 KING 5000 29025
7844 TURNER 1500 1500
7876 ADAMS 1100 1100
7900 JAMES 950 950
7902 FORD 3000 3800
7934 MILLER 1300 1300
14 rijen zijn geselecteerd.
Regards,
Rob. |
|
|
Posts:
986
Registered:
04/14/07
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 23, 2007 4:29 AM
in response to: Rob van Wijk
|
|
|
Volder,
A slightly easier query to do the same:
agree, nice one  |
|
|
Posts:
940
Registered:
08/09/06
|
|
|
|
Re: Sum upto Highest parent in Hierarchy
Posted:
Jul 23, 2007 5:10 AM
in response to: Volder
|
|
|
2. What for do you build hierarchy before? You're
going through the hierarchy in the right order (from
the leaf nodes to the parent nodes) by using
AUTOMATIC RULES ORDER.
So it is not necessary to use CONNECT BY and LEVEL as
one of the dimensions, IMHO:
I tried something like the query you showed but I was having an error ( something like automatic order not capable for such a request ) but now I can't reproduce that case.
I was thinking it was because of problems on aggregation so I tried that way, but now I forgot the exact text of previous query.
Anyway it's good to know that there was no need for hierarchical subquery first.
Bye Alessandro |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
