SQL & PL/SQL

Hi,

I've got stuck with this problem since quite some time, so pls help:

I am basically trying to rollup/aggregate values from the lower level nodes to the upper level nodes in a hierarchy. But this aggregate is a bit different than a plain rollup of values.

create table ent_rel (parent number, child number, child_amount number);

insert into ent_rel
select null, 100, null from dual
union all select 100, 101, null from dual
union all select 101, 102, 20 from dual
union all select 102, 103, null from dual
union all select 103, 104, null from dual
union all select 104, 105, null from dual
union all select 105, 106, 40 from dual
union all select 106, 107, null from dual
union all select 107, 108, 20 from dual
union all select 107, 109, 10 from dual
union all select 101, 203, null from dual
union all select 203, 205, 50 from dual
union all select 205, 207, null from dual
union all select 207, 209, null from dual
union all select 209, 210, 10 from dual
;

commit;

select RPAD(' ', (LEVEL - 1) * 2, '-') || child child
,level
,child_amount
from ent_rel
start with parent is null
connect by prior child = parent

CHILD LEVEL CHILD_AMOUNT
--------------------------------- ------ -------------
100 1
--101 2
----102 3 20
------103 4
--------104 5
----------105 6
------------106 7 40
--------------107 8
----------------108 9 20
----------------109 9 10
----203 3
------205 4 50
--------207 5
----------209 6
------------210 7 10

Now, I need a SQL query or even a simple PL/SQL code to get the DESIRED_AMOUNT field as shown below:

Pls note that there might be many more parallel branches to a node, but I've kept it simple just to get the concept.

CHILD LEVEL CHILD_AMOUNT DESIRED_AMOUNT
--------------------------------- ------ ------------- --------------
100 1 70
--101 2 70
----102 3 20 20
------103 4 40
--------104 5 40
----------105 6 40
------------106 7 40 40
--------------107 8 30
----------------108 9 20 20
----------------109 9 10 10
----203 3 50
------205 4 50 50
--------207 5 10
----------209 6 10
------------210 7 10 10

Thanks a lot in advance.

thanks and regards,
Knl