Skip to Main Content
Forums

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.

full outer joins

623129May 14 2008 — edited May 14 2008
Hi,
Can we write a full outer join using oracle properitory join syntax?

Regards,
Sharath

Comments

94799
See replies to similar problem...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4274185613870#69744480450437
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
541081
thank you but i can not use pl/sql in here

C.
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..
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?

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
Laurent Schneider
this will not work in 9i
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
_

Laurent Schneider
sounds good...
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.
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

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.
94799
Did you read the AskTom link yet?
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 ...
John Spencer
Laurent:

"Not sure what does 0 Health mean" In most gaming contexts, which this seems to be, it usually means dead :-)

john
Laurent Schneider
yes, I know the feeling 8-)
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 11 2008
Added on May 14 2008
6 comments
3,255 views