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.
group by TA.id
in the second example. instead ofunique
TA
andT1
? The query is confusing me a bit...