?
Solved

How to correct ORA-00918- Column ambiguously defined

Posted on 2007-11-29
2
Medium Priority
?
2,423 Views
Last Modified: 2013-12-07
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
Comment
Question by:ewgf2002
2 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20373262
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
 
LVL 14

Assisted Solution

by:GGuzdziol
GGuzdziol earned 1000 total points
ID: 20373280
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question