All,
I’m on Toad for Oracle 12.10.0.25.
When refactoring a query with Ansi joins to Oracle joins the generated query generates an error on Oracle.
The orginal query holds multiple left outer joins; after conversion I get ORA-01417.
Here is the input query with Ansi joins:
select coh.cono
, cor.rowpos
, cor.rowsubpos
, coh.yourcono
, coh.codate
, cor.pono
, COR.partno
, upper(cor.partdescr1) partdescr1
, cor.qtyco
, coalesce(cor.vendno, poc5.vendno) vendno – either vendno from order or vendno from main supplier
, por.vendpartno
, por.vendpartdescr1
, vb.name1 – name Supplier
, decode(por.pono – If Purchase order not in COROW then need to search for product in POROW
, null, case
when por.number_of_purchaseorders = 1 then to_char(por.pono)
when por.number_of_purchaseorders = 0 then ‘No Open Purchase order’
when por.number_of_purchaseorders is null then ‘No Open Purchase order’
else ‘Multiple orders !!!’
end
, por.pono
)
as purchaseorder
, decode(por.poadate
, null, case
when por.number_of_purchaseorders = 1 then to_char(por.poadate)
when por.number_of_purchaseorders = 0 then ‘No open Purchase order’
when por.number_of_purchaseorders is null then ‘No open Purchase order’
else ‘Multiple orders !!!’
end
, por.poadate
)
as deliverydate
, decode(por.rowstatus
, null, case
when por.number_of_purchaseorders = 1 then to_char(por.rowstatus)
when por.number_of_purchaseorders = 0 then ‘No open Purchase order’
when por.number_of_purchaseorders is null then ‘No open Purchase order’
else ‘Multiple orders !!!’
end
, por.rowstatus
)
as rowstatus
, decode(por.purchaser
, null, case
when por.number_of_purchasers = 1 then p.czwinlogin
when por.number_of_purchasers = 0 then ‘No Purchasers’
when por.number_of_purchasers is null then ‘No Purchasers’
else ‘Multiple Purchasers !!!’
end
, p1.czwinlogin
)
as purchaser
from ehda.corow cor
left outer join ehda.cohead coh on coh.cono = cor.cono
left outer join ehda.custbuy cb on cb.custno = coh.custno
left outer join ehda.a_localpart lp – determine class5 to find main supplier
on lp.partno = cor.partno
and lp.compno = cb.compno
left outer join utl_krg.companies cmp on cmp.compno = cb.compno – determine headquarter to find main supplier
left outer join ehda.a_poveclass5 poc5 – determine main supplier
on poc5.class5 = lp.class5
and poc5.compno = cmp.hq_compno
left outer join ehda.vendbuy vb on vb.vendno = coalesce(cor.vendno, poc5.vendno)
left join --get data from purchase order registered in orderrow
(select por1.vendpartno
, por1.vendpartdescr1
, por1.vendpartdescr2
, poh1.pono
, poh1.purchaser
, por1.poadate
, por1.rowstatus
from ehda.porow_mv por1 inner join ehda.pohead_mv poh1 on poh1.pono = por1.pono) por1
on cor.pono = por1.pono
and cor.partdescr2 = por1.vendpartno
left outer join – get data from purchase rows from open purchase orders for regarding product
(select por.partno
, por.vendpartdescr2
, min(poh.pono) as pono
, min(por.poadate) keep (dense_rank first order by poh.pono) as poadate
, min(por.rowstatus) keep (dense_rank first order by poh.pono) as rowstatus
, min(poh.purchaser) keep (dense_rank first order by poh.pono) as purchaser
, min(por.vendpartno) as vendpartno
, min(por.vendpartdescr1) as vendpartdescr1
, count(distinct poh.pono) as number_of_purchaseorders
, count(distinct poh.purchaser) as number_of_purchasers
from ehda.porow_mv por inner join ehda.pohead_mv poh on poh.pono = por.pono
where por.rowstatus >= ‘300’
and por.rowstatus <= ‘330’
group by por.partno, por.vendpartdescr2) por
on por.partno = cor.partno
and case when cor.partno like ‘SPEC%’ then upper(por.vendpartdescr2) else upper(cor.partdescr2) end = upper(cor.partdescr2)
left outer join ehda.pers p on p.employno = por.purchaser
left outer join ehda.pers p1 on p1.employno = por1.purchaser;