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

    advance connect by question

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

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4274185613870#69744480450437
          • 2. Re: advance connect by question
            jeneesh
            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
            start with p_id is null
            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
              541081
              thank you but i can not use pl/sql in here

              C.
              • 4. Re: advance connect by question
                jeneesh
                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
                    start with p_id is null
                    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
                  ushitaki
                  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)
                      from abc t2 start with t2.id = t1.id
                      connect by prior p_id = id) calc_health
                  from abc t1
                  start with p_id is null
                  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
                    Laurent Schneider
                    See replies to similar problem...

                    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P1
                    1_QUESTION_ID:4274185613870#69744480450437
                    ;-) nice try

                    what about this :
                    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
                      Laurent Schneider
                      this will not work in 9i
                      • 8. Re: advance connect by question
                        ushitaki
                        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
                          Laurent Schneider
                          sounds good...
                          • 10. Re: advance connect by question
                            94799
                            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
                              Avinash Tripathi
                              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
                                4        START WITH id =abc.id
                                5        CONNECT BY PRIOR  p_id = id
                                6        ) SUM_HEALTH
                                7  FROM t ABC
                                8  START WITH p_id is NULL
                                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
                                541081
                                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.
                                • 13. Re: advance connect by question
                                  94799
                                  Did you read the AskTom link yet?
                                  • 14. Re: advance connect by question
                                    Laurent Schneider
                                    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