ORA-00918 column ambiguously defined in SELECT

Question

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  lt   25   Any suggestions please

User · Answer

You can also see this error when selecting for a union where corresponding columns can be null.

select * from (select D.dept_no, D.nullable_comment
                  from dept D
       union
               select R.dept_no, NULL
                 from redundant_dept R
)

This apparently confuses the parser, a solution is to assign a column alias to the always null column.

select * from (select D.dept_no, D.comment
                  from dept D
       union
               select R.dept_no, NULL "nullable_comment"
                 from redundant_dept R
)

The alias does not have to be the same as the corresponding column, but the column heading in the result is driven by the first query from among the union members, so it's probably a good practice.

User · Answer

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

User · Answer

You have multiple columns named the same thing in your inner query  so the error is raised in the outer query   If you get rid of the outer query  it should run  although still be confusing   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  lt   25   It would be much better  for readability and performance both  to specify exactly what fields you need from each of the tables instead of selecting them all anyways   Then if you really need two fields called the same thing from different tables  use column aliases to differentiate between them

[sql] ORA-00918: column ambiguously defined in SELECT *

Examples related to sql

Examples related to oracle

Examples related to ora-00918