Chema Chema - 3 months ago 15
SQL Question

Invalid identifier in OVER Oracle

I'm having this query:

SELECT *
FROM (SELECT ORDEN.* ,ROW_NUMBER() OVER (ORDER BY HDC_FEC) AS FILE
FROM (SELECT HDC_OPP AS numberOperation, HDC_FEC AS SystemDate,ESD_DES AS state,HDC_MTV AS motiveCode FROM HDC
LEFT JOIN TRM ON HDC.HDC_TRM = TRM.TRM_COD
...


and I'm getting an error 'invalid identifier HDC_FEC in OVER clauses. If I replace by SystemDate works fine. Why I can't use HDC_FEC instead of SystemDate?

Thank you.

Answer

You are giving the HDC_FEC column the alias SystemDate in the subquery, so you can only refer to that alias in the outer query:

SELECT *
FROM (SELECT ORDEN.* ,ROW_NUMBER() OVER (ORDER BY SystemDate) AS FILE

The original underlying table columns are not visible - they are out of scope. You can only see what you have exposed in the subquery.

It isn't clear from what you've shown if you actually need the second level of subquery. If you don't have a where clause against the subquery (where ORDEN.some_col = ...) then you can move the row_number() into the same level of query. You may also need the partition by clause, but again it isn't clear. (Perhaps you've incorrectly created the second level of subquery so you can add group by when you really want a partition...?)