Using an Alias in a WHERE clause

Question

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  gt  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

User · Answer

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  gt  UPD DATE   Kudos to Syed Mehroz Alam

User · Answer

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  gt  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

User · Answer

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  gt  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

User · Answer

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  gt  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

User · Answer

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  gt  UPD DATE

User · Answer

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  gt  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

User · Answer

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  gt  UPD DATE   Kudos to Syed Mehroz Alam

User · Answer

Or you can have your alias in a HAVING clause

User · Answer

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  gt  UPD DATE

User · Answer

Or you can have your alias in a HAVING clause

User · Answer

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  gt  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

User · Answer

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  gt  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

[sql] Using an Alias in a WHERE clause

Examples related to sql

Examples related to oracle

Examples related to alias

Examples related to decode

Examples related to ora-00904