1 2 Previous Next 16 Replies Latest reply on Feb 9, 2007 4:29 PM by Laurent Schneider

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
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.
• ###### 1. Re: advance connect by question
See replies to similar problem...

• ###### 2. Re: advance connect by question
One method
```sql>
create or replace function fn1(p1 varchar2) return number
as
a number;
begin
execute immediate 'select '||p1||' from dual' into a;
return a;
end;
Function created.
sql>
select id,p_id,health,fn1(str)
from(
select id, p_id, health, level lvl,'0'||sys_connect_by_path(health,'+') str
from t
connect by prior id=p_id);
ID P_ID HEALTH FN1(STR)
1     10  10
2  1  20  30
4  2  15  45
3  1  30  40

jeneesh                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                ```
• ###### 3. Re: advance connect by question
thank you but i can not use pl/sql in here

C.
• ###### 4. Re: advance connect by question
```sql>
select id,p_id,health,lvl,
sum(to_number(substr(str,instr(str,'+',1,n)+1,(instr(str,'+',1,n+1))-(instr(str,'+',1,n)+1)))) sm
from(
select id, p_id, health, level lvl,sys_connect_by_path(health,'+')||'+' str
from t
connect by prior id=p_id) t1,(select rownum n
from t) t2
where t2.n <=  t1.lvl
group by id,p_id,health,lvl;
ID P_ID HEALTH LVL SM
1     10  1  10
2  1  20  2  30
3  1  30  2  40
4  2  15  3  45

Message was edited by:
jeneesh
Formatted..                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        ```
• ###### 5. Re: advance connect by question
```with abc as
(select 1 Id, cast(null as number) p_id, 10 health from dual
union all
select 2,1,20 from dual
union all
select 3,1,30 from dual
union all
select 4,2,15 from dual
)
/* Main */
select id, p_id, health, level
,(select sum(health)
connect by prior p_id = id) calc_health
from abc t1
connect by prior id=p_id
;

ID       P_ID     HEALTH      LEVEL CALC_HEALTH
---------- ---------- ---------- ---------- -----------
1                    10          1          10
2          1         20          2          30
4          2         15          3          45
3          1         30          2          40```
This is inefficient, but is suitable for you?
• ###### 6. Re: advance connect by question
See replies to similar problem...

1_QUESTION_ID:4274185613870#69744480450437
;-) nice try

``````with abc as
(select 1 Id, cast(null as number) p_id, 10 health from dual
union all
select 2,1,20 from dual
union all
select 3,1,30 from dual
union all
select 4,2,15 from dual
)
/* Main */
select id,p_id,health,l "LEVEL", calc_health from (
select r,id,p_id,health,l, sum(ch) over (partition by id) calc_health,cp from (
select rownum r,id, p_id, health, level l, connect_by_root health ch, connect_by_root p_id cp
from abc t1
connect by prior id=p_id))
where cp is null
order by r
/

ID       P_ID     HEALTH      LEVEL CALC_HEALTH
---------- ---------- ---------- ---------- -----------
1                    10          1          10
2          1         20          2          30
4          2         15          3          45
3          1         30          2          40``````
• ###### 7. Re: advance connect by question
this will not work in 9i
• ###### 8. Re: advance connect by question
In query that I wrote, a:b is 1:n is assumed to be a precondition.
In such assumption, it is possible to be written a little more simple,
and not necessary to be applied 'connect by' twice.

thus,
```/* Main */
select id,p_id,health
,max(lvl) as "LEVEL"
,sum(calc_health) calc_health
from (
select
connect_by_root(id) id
,connect_by_root(p_id) p_id
,connect_by_root(health) health
,level lvl
,health calc_health
from abc
connect by prior p_id = id
)
group by id,p_id,health
;```
(This works over Oracle10g, 'cause of connect_by_root function)
Message was edited by:
ushitaki
• ###### 9. Re: advance connect by question
sounds good...
• ###### 10. Re: advance connect by question
Well yes but you are now traversing and summing the entire hierarchy from the child nodes upwards.

I don't think this is going to be very useful or efficient if you want to start at a given parent node and traverse down the hierarchy.
• ###### 11. Re: advance connect by question
Hi,

This will work is 9i also.
```SQL> SELECT * FROM t;

ID       P_ID     HEALTH
---------- ---------- ----------
1                    10
2          1         20
3          1         30
4          2         15

SQL>

SQL> ed
Wrote file afiedt.buf

1  SELECT id, p_id, health, LEVEL LVL,
2      ( SELECT  SUM(health)
3        FROM  t
5        CONNECT BY PRIOR  p_id = id
6        ) SUM_HEALTH
7  FROM t ABC
9* CONNECT BY PRIOR  id = p_id
10  /

ID       P_ID     HEALTH        LVL SUM_HEALTH
---------- ---------- ---------- ---------- ----------
1                    10          1         10
2          1         20          2         30
4          2         15          3         45
3          1         30          2         40

SQL> ```
Regards
• ###### 12. Re: advance connect by question
Sorry, it is a little more complicated.

Let say I got
Id ----- p_id ----- health ----- multi
1 ------ null ------ 10 --------- 2
2 --------1 --------- 0 ---------- 5
3 -------- 1 --------- 0 ---------3
4 -------- 2 ----------0 ---------4

and i need to get this
Id----p_id----health----level----multi ------ calc
-------------------------------------------------------
1 ----null----10---------1--------2------------2*10
2 ----1-------0 ----------2---------5-----------20*5
3 ----1-------0 ----------2---------3-----------20*3
4 ----2-------0 ----------3---------4-----------100*4

the calcolation i need to do is much more complecated, but the base of the calcolation is the calc field of the father.

Thank you
C.
• ###### 14. Re: advance connect by question
out of the solutions given above, you could try
``````with abc as
(select 1 Id, cast(null as number) p_id, 10 health, 2 multi from dual
union all
select 2,1,0,5 from dual
union all
select 3,1,0,3 from dual
union all
select 4,2,0,4 from dual
)
/* Main */
select id,p_id,health,max(lvl) "LEVEL",multi,max(xhealth)*exp(sum(ln(xmulti))) calc
from (
select
connect_by_root id id
,connect_by_root p_id p_id
,connect_by_root health health
,connect_by_root multi multi
,level lvl
,health xhealth
,multi xmulti
from abc
connect by prior p_id = id
)
group by id,p_id,multi,health
order by id
/
ID       P_ID     HEALTH      LEVEL      MULTI       CALC
---------- ---------- ---------- ---------- ---------- ----------
1                    10          1          2         20
2          1          0          2          5        100
3          1          0          2          3         60
4          2          0          3          4        400``````
Not sure what does 0 Health mean ...
1 2 Previous Next