using oracle 9.2
I'm converting a statement using the left outer join symbol (+) in the WHERE clause, to the ANSI standard method doing the join in the FROM clause.
I'm I doing this convertion right?
It just seems way too convoluted.
--------------------------------------------------
select count(mmatter)
from ware.matter,
(select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=6) BrokDept,
(select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=13) ProdCode,
(select uddate,udjoin from ware.udf where udtype='MT' and udfindex=11) ProjEndDt,
(select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=10) RenewalDt
where matter.mmatter=BrokDept.udjoin(+) and
matter.mmatter=ProdCode.udjoin(+) and
matter.mmatter=ProjEndDt.udjoin(+) and
matter.mmatter=RenewalDt.udjoin(+);
Convert to...
select count(ware.matter.mmatter)
from (((ware.matter left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=6) BrokDept on matter.mmatter=BrokDept.udjoin)
left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=13) ProdCode
on matter.mmatter=ProdCode.udjoin)
left outer join (select uddate,udjoin from ware.udf where udtype='MT' and udfindex=11) ProjEndDt
on matter.mmatter=ProjEndDt.udjoin)
left outer join (select udvalue,udjoin from ware.udf where udtype='MT' and udfindex=10) RenewalDt
on matter.mmatter=RenewalDt.udjoin;