How to Avoid ORA-00904 Error When Using an Alias in a SQL WHERE Clause?

Use aliases in WHERE clauses

Question

A SQL query designed to display the number of rows in a table that were not recently updated encountered an ORA-00904 error due to the use of an alias in the WHERE clause. Users are looking for a solution to this problem without repeating the complex DECODE function in the WHERE clause.

Solution

Using aliases directly in the WHERE clause is not feasible because WHERE is executed before SELECT, which is the last step of query execution. However, you can use a subquery as a workaround:

<code class="language-sql">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</code>

This subquery approach effectively allows filtering based on alias expressions without encountering the initial error.

The above is the detailed content of How to Avoid ORA-00904 Error When Using an Alias in a SQL WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!