I have been writing some scripts in oracle and have just encountered and solved a niggly little problem. Syntax difference between SQL Server and Oracle threw me.

On performing this query, was receiving this error: 00904. 00000 -  “%s: invalid identifier”.

WITH tempTab AS
(
SELECT a,b,c,d,e, ROW_NUMBER() OVER (partition by a ORDER BY e) AS “RowNums”
FROM tableName
ORDER BY aASC
)
SELECT * From tempTab WHERE RowNums = 1

Because the RowNums is a created column name, the referred RowNums outside of the WITH clause must also be referenced in the same manner i,e “RowNums”

Logical right? ;P