Toad World® Forums

Refactor Ansi join to Oracle join generates incorrect query

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;