Using an Alias in a WHERE clause

alias, decode, ora-00904, oracle, sql

Solution

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

Problem

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...)

Original source