ORA-00918: column ambiguously defined in SELECT *

sql oracle ora-00918
user43685 picture user43685 · Jun 4, 2011 · Viewed 213.5k times · Source

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 JOIN organizations_users ON organizations_users.user_id = users.id
) WHERE rownum <= 25

Any suggestions please?

Answer

APC picture APC · Jun 4, 2011

A query's projection can only have one instance of a given name. As your WHERE clause shows, you have several tables with a column called ID. Because you are selecting * your projection will have several columns called ID. Or it would have were it not for the compiler hurling ORA-00918.

The solution is quite simple: you will have to expand the projection to explicitly select named columns. Then you can either leave out the duplicate columns, retaining just (say) COACHES.ID or use column aliases: coaches.id as COACHES_ID.

Perhaps that strikes you as a lot of typing, but it is the only way. If it is any comfort, SELECT * is regarded as bad practice in production code: explicitly named columns are much safer.

Related questions

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
Mixing "USING" and "ON" in Oracle ANSI join

I wrote an Oracle SQL expression like this: SELECT ... FROM mc_current_view a JOIN account_master am USING (account_no) JOIN account_master am_loan ON (am.account_no = am_loan.parent_account_no) JOIN ml_client_account mca …

Read More