Menu
I have a query SELECT A.ID, B.ID FROM A, B that works fine.
As soon as I add FETCH FIRST n ROWS ONLY to it, the query fails with the error message
SQL Error [918] [42000]: ORA-00918: column ambiguously defined
As far as I understand, the error refers to an ambiguous SELECT clause and should not be caused by a FETCH FIRST n ROWS ONLY.
Do I miss something that justifies this behaviour? Or is this a bug?
I know that I can omit this behaviour when I specify an explicit column alias. I want to know, why SELECT A.ID, B.ID FROM A, B works, while SELECT A.ID, B.ID FROM A, B FETCH FIRST 10 ROWS ONLY doesn't.
The Oracle version is 12.1.0.2.0
844
Luckily the solution to this Oracle error is just about as straightforward as finding what causes the problem. What is needed is to add the prefix to each column with the table name that it originally belonged too and then re-execute the SQL statement.
Check to see if this table has column STATUS or ID_TYPE (or perhaps both). Then find what other table or tables has/have columns with the same name(s). To fix the problem, decide from which table you should select those columns. You shouldn't have any unqualified column names in a query like this.
Ora-00904 Error Message “Invalid Identifier” This error is most common when querying a SELECT statement. To resolve this error, first check to make sure the column name being referenced exists. If it does not exist, you must create one before attempting to execute an SQL statement with the column.
ORA-00979 “ Not a GROUP BY expression ” is an error issued by the Oracle database when the SELECT statement contains a column that is neither listed in GROUP BY nor aggregated. This error message can be confusing to beginners.
This is documented in oracle documents:
Restrictions on the row_limiting_clause
If the select list contains columns with identical names and you specify the row_limiting_clause, then an ORA-00918 error occurs. This error occurs whether the identically named columns are in the same table or in different tables. You can work around this issue by specifying unique column aliases for the identically named columns.
Even though SELECT query works, after using FETCH FIRST|NEXT, it will throw error if two of the column names are same.
You should just assign different alias names for all columns in SELECT clause.
61
Learn to use proper, explicit, **standard* JOIN syntax. Never use commas in the FROM clause. But that is not your problem.
You have two columns with the same alias. Simply use as to assign new aliases:
SELECT A.ID as a_id, B.ID as b_id
What you are observing may be a bug. The code seems to work on other versions of Oracle.
26
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
