Home » SQL & PL/SQL » SQL & PL/SQL » oracle tree calculation (Oracle 10)
oracle tree calculation Fri, 12 February 2010 14:41
 rogermourad Messages: 3Registered: 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
 BlackSwan Messages: 25417Registered: 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
 rogermourad Messages: 3Registered: 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
 BlackSwan Messages: 25417Registered: 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
 rogermourad Messages: 3Registered: 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
 Michel Cadot Messages: 64823Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Here's a hint:
2         ( select sum(sal)
3           from emp e2
4           connect by prior empno = mgr
6  from emp
7  connect by prior empno = mgr
9  /
ENAME                  SAL    SUM_SAL
--------------- ---------- ----------
KING                  5000      29025
--JONES               2975      10875
----SCOTT             3000       4100
----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: Wed May 24 16:41:47 CDT 2017

Total time taken to generate the page: 0.15340 seconds