2
procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select T1.*
from T1
where T1.x=p1
    and (   
        p2 in (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
    or
        not exists (SELECT z from T2 where T2.someId=T1.SomeId and T2.y=12)
        );
end;

The subquery is the exact same both times. Is there a way to refactor it to be specified only once? On Oracle at least.

One way would be to use it as a join:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
select unique T1.*
from T1
left join T2 on T2.someId=T1.SomeId and T2.y=12
where T1.x=p1
    and (   
        p2 = T2.z
    or
        T2.z is null -- T2.z is a NOT NULL column
        );
end;

But this is not equivalent (I added UNIQUE to filter out rows multiplied due to join, but this also removes possible correct duplicate rows from T1).

PS: Made an SQL Fiddle with the two queries from above and from the two answers below.

3
  • don't know if I understand correctly, but why not group by TA.id in the second example. instead of unique Dec 3 '15 at 22:21
  • Did you mix up your naming of TA and T1? The query is confusing me a bit...
    – Chris
    Dec 3 '15 at 22:37
  • @Chris yes, sorry. I fixed it now. Dec 3 '15 at 23:28
1

I came up with this:

procedure foo(p1 IN INTEGER,
    p2 IN INTEGER,
    ooo out sys_refcursor)
is
begin

open ooo for
with sq as (SELECT z, someId from T2 where T2.y=12)
select T1.*
from T1
where T1.x=p1
    and (   
        (p2,T1.someId) in (select * from sq)
    or
        not exists (select * from sq where sq.someId=T1.someId)
        );
end;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.