advance connect by question
541081Feb 7 2007 — edited Feb 9 2007I got this table
Id p_id health
------- --------- -------
1 null 10
2 1 20
3 1 30
4 2 15
i need to use connect by to connect all the child to the parent id (p_id) and show the level. i know how to do this far.
select id, p_id, health, level lvl
from abc
start with p_id is null
connect by prior p_id=id
But I need also calculate health, so every row will have the health of itself + the health of the fathers.
For that example if p_id is null get only your health if not get your health with your father health.
Id----p_id----health----level----calc_health
--------------------------------------------------
1 ----null----10---------1--------10
2 ----1-------20---------2---------20+10
3 ----1-------30---------2---------30+10
4 ----2-------15---------3---------15+20+10
How can I do that?
I can use only sql with out pl/sql
Thank you
C.