• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2431
  • Last Modified:

How to correct ORA-00918- Column ambiguously defined

Need to correct this query
select matrectrans.itemnum,matrectrans.LINETYPE, matrectrans.GLDEBITACCT,
matrectrans.assetnum,matrectrans.location,
matrectrans.tostoreloc, matrectrans.ISSUETYPE,matrectrans.refwo,
matrectrans.ponum, matrectrans.quantity,
matrectrans.linecost, matrectrans.description,matrectrans.status,
matrectrans.siteid,matrectrans.actualdate, po.vendor, companies.name
from matrectrans left outer join po
on matrectrans.ponum = po.ponum
and matrectrans.siteid = po.siteid,
companies left outer join po
on po.orgid = companies.orgid
0
ewgf2002
Asked:
ewgf2002
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you query should rather go like this:
select matrectrans.itemnum,matrectrans.LINETYPE, matrectrans.GLDEBITACCT, 
matrectrans.assetnum,matrectrans.location,
matrectrans.tostoreloc, matrectrans.ISSUETYPE,matrectrans.refwo,
matrectrans.ponum, matrectrans.quantity,
matrectrans.linecost, matrectrans.description,matrectrans.status,
matrectrans.siteid,matrectrans.actualdate, po.vendor, companies.name
from matrectrans 
left outer join po 
on matrectrans.ponum = po.ponum
and matrectrans.siteid = po.siteid
left outer join companies 
   on po.orgid = companies.orgid

Open in new window

0
 
GGuzdziolCommented:
You use po table twice in outer join; I guess you wanted

from matrectrans left outer join po
on matrectrans.ponum = po.ponum and matrectrans.siteid = po.siteid
right outer join companies on po.orgid = companies.orgid

(or maybe left outer join? I do not know your logic, but I'm pretty sure you need po table once; this will give you proper data and get rid of ORA-00918. If you need po table twice in from clause then you have to alias it, i.e.
from ... left outer join po po1 on ... left outer join po po2 on ...)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now