-
i want to get sum in tree for each node with childs
tree :
A(1)_B(4)
|
|_C(1)_E(3)
|
|_D(2)
sum num for each node and childs
---------------------
A 11(4+4+2+1)
B 4
C 4(1+3)
D 2
E 3
------------------------
structure is:
srl
ogcost_srl --relation to parent
title
------------------------
sql:
select title,f_sum(srl)
from ogcostt a
-------------------------
f_sum:
f_sum(cur_srl numer) return number is
sum_num number(6);
begin
select sum(num)
into sum_num
from admin.ogcostt
start with srl=cur_srl
connect with prior srl=ogcost_srl);
return sum_num;
end;
------------------------
this method calculate repeated sum and not effichient
and time consuming
can i use from analytic functin for efficient sql?
-
subquery?
If you have Oracle 8.1.6 or above then you can use the following form of subquery:
select title,
(select sum(num) from ogcostt
connect by prior srl=ogcost_srl
start with srl=a.srl)
from ogcostt a;
I don't think, though, that it is much more efficient. Try.
Hope it helps.
Tomaž
-
hi tomazZ
thank you for your answer.
but this approach also repeating sum for high level in tree;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|