Skip to Main Content

advance connect by question

541081Feb 7 2007 — edited Feb 9 2007
I 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.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked due to inactivity on Mar 9 2007
Added on Feb 7 2007
16 comments
2,683 views