Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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 on Mar 9 2007
Added on Feb 7 2007
16 comments
2,810 views