Saturday, November 30, 2013

ORA-00960:Ambiguous column naming in select list

Ambiguous column error in oracle is a very familiar one as almost every oracle developer come across this error. But I recently got slapped by this error when I had duplicate column names in sub-query.
Let us go through a simple example on this,

The below query returns two columns and both with the same name TO_DATE,

SELECT SYSDATE TO_DAY, SYSTIMESTAMP TO_DAY
FROM   DUAL;



It works perfectly fine. But the moment when you put the same query as a sub-query of another like the one below oracle will show a RED card on you.

SELECT *
FROM  (SELECT SYSDATE TO_DAY, SYSTIMESTAMP TO_DAY
               FROM    DUAL);


I was taken aback by this error for a moment and then it struck my mind why I got this error. Its fine to have duplicate columns in the main query. But the moment it becomes a sub-query, it will be treated like a table (in-line view). We can't have duplicate columns in a table/view. So it make sense to get an error when your sub-query have duplicate column names.