sql - Use aliases in the where clause
I have a query which is meant to show me any rows in table A which have not been updated recently enough. (Each row should be updated within 2 months after "month_no".):
SELECT A.identifier
, A.name
, TO_NUMBER(DECODE( A.month_no
, 1, 200803
, 2, 200804
, 3, 200805
, 4, 200806
, 5, 200807
, 6, 200808
, 7, 200809
, 8, 200810
, 9, 200811
, 10, 200812
, 11, 200701
, 12, 200702
, NULL)) as MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B
WHERE A.identifier = B.identifier
AND MONTH_NO > UPD_DATE
The last line in the WHERE clause causes an "ORA-00904 Invalid Identifier" error. Needless to say, I don't want to repeat the entire DECODE function in my WHERE clause. Any thoughts? (Both fixes and workarounds accepted...)
This question and all comments follow the
"Attribution Required."
Similar questions
- sql - Can you use aliases in where clause of MySQL?
- sql - Using column aliases in the where clause of a MySQL query results in an error
- SQL - Use Max in where clause
- sql server - SQL - use aliases in the where clause of a subquery
- sql - How to use "and" and "or" in "where" clause
- More similar questions >>
All Answers
Leave a Reply