Home » SQL & PL/SQL » SQL & PL/SQL » oracle tree calculation (Oracle 10)
oracle tree calculation [message #443117] Fri, 12 February 2010 14:41 Go to next message
rogermourad
Messages: 3
Registered: February 2010
Junior Member
hi,
i have this table
emp sum_sales manager child
10 0 0
11 130 10 1
12 260 11 2
13 0 12 3

the number of children is not limited.

i need to calculate the comm of the employees: the result should be like that:
emp10=(sum_sales of emp 11 * child 1 perc)+(sum_sales of emp 12 * child 2 perc) + (sum_sales of emp 13 * child 3 perc)

emp11=(sum_sales of emp 12 * child 2 perc) + (sum_sales of emp 13 * child 3 perc)

emp12 = (sum_sales of emp 13 * child 3 perc)

how should i write the script to get this result your help is really appreciated

tank you
Re: oracle tree calculation [message #443118 is a reply to message #443117] Fri, 12 February 2010 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: oracle tree calculation [message #443119 is a reply to message #443118] Fri, 12 February 2010 14:50 Go to previous messageGo to next message
rogermourad
Messages: 3
Registered: February 2010
Junior Member
the table is

temp_sumorders

Emp Sum_sales Manager Child
10 0 Null 0
11 130 10 1
12 260 11 2
13 0 12 3


the output should be:
comm emp10=130*perc(1)+260*perc2+0*perc3
comm emp11=260*perc1+0*perc3
comm emp12=0*perc1

Re: oracle tree calculation [message #443120 is a reply to message #443119] Fri, 12 February 2010 15:11 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
0*anything = 0
Re: oracle tree calculation [message #443121 is a reply to message #443120] Fri, 12 February 2010 15:23 Go to previous messageGo to next message
rogermourad
Messages: 3
Registered: February 2010
Junior Member
i need help and is an example and i meant by this that the loop will calculate till the last child
Re: oracle tree calculation [message #443154 is a reply to message #443117] Sat, 13 February 2010 01:19 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a hint:
SQL> select rpad('-',2*(level-1),'-')||ename ename, sal,
  2         ( select sum(sal) 
  3           from emp e2 
  4           connect by prior empno = mgr 
  5           start with e2.empno = emp.empno ) sum_sal
  6  from emp
  7  connect by prior empno = mgr
  8  start with mgr is null
  9  /
ENAME                  SAL    SUM_SAL
--------------- ---------- ----------
KING                  5000      29025
--JONES               2975      10875
----SCOTT             3000       4100
------ADAMS           1100       1100
----FORD              3000       3800
------SMITH            800        800
--BLAKE               2850       9400
----ALLEN             1600       1600
----WARD              1250       1250
----MARTIN            1250       1250
----TURNER            1500       1500
----JAMES              950        950
--CLARK               2450       3750
----MILLER            1300       1300

If you post a working Test case: create table and insert statements along with the result you want with these data, we will be able to work with them.

Regards
Michel
Previous Topic: model-clause
Next Topic: Summing of time
Goto Forum:
  


Current Time: Sun Dec 04 04:39:54 CST 2016

Total time taken to generate the page: 0.11820 seconds