Using an Alias in a WHERE clause

by

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

More Related Contents:

  1. ORA-00904: invalid identifier
  2. Using ‘case expression column’ in where clause
  3. How to use BOOLEAN type in SELECT statement
  4. ORA 00904 Error:Invalid Identifier
  5. Is it possible to query a comma separated column for a specific value?
  6. ORA-00972 identifier is too long alias column name
  7. Using Alias In When Portion of a Case Statement in Oracle SQL
  8. How to use Alias in Where clause? [duplicate]
  9. Why doesn’t Oracle SQL allow us to use column aliases in GROUP BY clauses?
  10. Receive an unexpected symbol error in my PLSQL query [closed]
  11. SQL Query to concatenate column values from multiple rows in Oracle
  12. Search All Fields In All Tables For A Specific Value (Oracle)
  13. Get top results for each group (in Oracle)
  14. Paging with Oracle
  15. Oracle Differences between NVL and Coalesce
  16. ORA-12560: TNS:protocol adaptor error
  17. SQL – How to store and navigate hierarchies?
  18. Oracle SQL, concatenate multiple columns + add text
  19. Oracle query to fetch column names
  20. INSERT SELECT statement in Oracle 11G
  21. SELECT INTO using Oracle
  22. SQL Oracle LEFT JOIN and SUBQUERY error: ORA-00905: missing keyword
  23. `show create table` equivalent in oracle sql
  24. Does ‘Select’ always order by primary key?
  25. How to use a dynamic parameter in a IN clause of a JPA named query?
  26. Return number from Oracle Select statement after parsing date
  27. How do you interpret a query’s explain plan?
  28. Oracle DB: How can I write query ignoring case?
  29. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  30. How to call Oracle MD5 hash function?

Leave a Comment