ORA-00918: column ambiguously defined: how to find the column

oracle ora-00918
Daniele picture Daniele · Sep 17, 2012 · Viewed 33k times · Source

I'm getting the classic error:

ORA-00918: column ambiguously defined

Usually, I know how to solve it but my problem now is that I'm working with a 700 row query.

Is there a way to identify the column?

Answer

Ricardo Arnold picture Ricardo Arnold · Sep 18, 2012

Have you tried to do a binary search?

e.g.

If your original query looks like

Select  col1 
       ,col2
       ,col3
       ,col4
from MyTable
  1. you can start with commenting the 2nd half
Select  col1
       ,col2
       /*,col3
       ,col4 */
from MyTable
  1. If you still get the error, run the query again commenting some column from the other half:
Select col1
       /*,col2 */
      ,col3
      ,col4
from MyTable

If you still get an error then your problem is with col1, otherwise you need to change col2.

Related questions

ORA-00918: column ambiguously defined in SELECT *

Getting ORA-00918: column ambiguously defined: running this SQL: SELECT * FROM (SELECT DISTINCT(coaches.id), people.*, users.*, coaches.* FROM "COACHES" INNER JOIN people ON people.id = coaches.person_id INNER JOIN users ON coaches.person_id = users.person_id LEFT OUTER …

Read More
SQL: How to find duplicates based on two fields?

I have rows in an Oracle database table which should be unique for a combination of two fields but the unique constrain is not set up on the table so I need to find all rows which violate the constraint …

Read More
Why doesn't Oracle raise "ORA-00918: column ambiguously defined" for this query?

I've just come across a strange behaviour in Oracle where I would expect ORA-00918 to be raised, but isn't. Take this query, for example. SELECT * FROM USER_TABLES TAB JOIN USER_TRIGGERS TRG ON TRG.TABLE_NAME = TAB.TABLE_NAME …

Read More