Using an Alias in a WHERE clause
SqlOracleAliasDecodeOra 00904Sql Problem Overview
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...)
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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
Solution 3 - Sql
Or you can have your alias in a HAVING clause
Solution 4 - Sql
Just as an alternative approach to you can do:
WITH inner_table AS
(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)
SELECT * FROM inner_table
WHERE MONTH_NO > UPD_DATE
Also you can create a permanent view for your queue and select from view.
CREATE OR REPLACE VIEW_1 AS (SELECT ...);
SELECT * FROM VIEW_1;
Solution 5 - Sql
It's possible to effectively define a variable that can be used in both the SELECT, WHERE and other clauses.
A subquery doesn't necessarily allow for appropriate binding to the referenced table columns, however OUTER APPLY does.
SELECT A.identifier
, A.name
, vars.MONTH_NO
, TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
FROM table_a A
, table_b B ON A.identifier = B.identifier
OUTER APPLY (
SELECT
-- variables
MONTH_NO = 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))
) vars
WHERE vars.MONTH_NO > UPD_DATE
Kudos to Syed Mehroz Alam.