Receiving ORA-00918 error when running SQL query

1143015 pts.
Tags:
ORA
Oracle
SQL Query
In Oracle, I keep getting this ORA-00918 error: Column ambiguously defined when I'm running this SQL query:
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
What should I do?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

Quite confusing, but it happens when you are including columns with the same name in the select list.  So, to fix it you would need to put an alias on some columns.

It is not easy to see it, though, because you are using * instead on enumerating the columns you need.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • clark445kent
    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.
    8,235 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: