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
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.