sum on tree structure

[rgharibnejad]

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?

[TomazZ]

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ž

[rgharibnejad]

hi tomazZ
thank you for your answer.
but this approach also repeating sum for high level in tree;