Menu
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...)
605
We cannot use a column alias with WHERE and HAVING clauses.
No you can't reference the column alias in the WHERE clause.
You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses. Standard SQL doesn't allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.
You can do a sub-select and filter on it:
SELECT * FROM ( 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 ) AS inner_table WHERE MONTH_NO > UPD_DATE Interesting bit of info moved up from the comments:
There should be no performance hit. Oracle does not need to materialize inner queries before applying outer conditions -- Oracle will consider transforming this query internally and push the predicate down into the inner query and will do so if it is cost effective. – Justin Cave
113
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 .identifier = B.identifier HAVING MONTH_NO > UPD_DATE If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
