在WHERE子句中使用别名 [英] Using an Alias in a WHERE clause

问题描述

我有一个查询，旨在向我显示表A中最近更新不够的任何行. (每行应在"month_no"之后的2个月内进行更新.):

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

WHERE子句中的最后一行导致"ORA-00904无效标识符"错误.不用说，我不想在WHERE子句中重复整个DECODE函数.有什么想法吗? (接受了所有修复程序和解决方法...)

解决方案

这不可能直接实现，因为按时间顺序，WHERE发生在 之前，这始终是执行链中的最后一步. /p>

您可以进行子选择并对其进行过滤:

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

有趣的信息从评论中移了出来:

应该不会影响性能. Oracle不需要实现 在应用外部之前进行内部查询 条件-Oracle将考虑 内部转换此查询，并 将谓词向下推入内部 查询，如果需要的话，会这样做 有效的. – 贾斯汀·凯夫

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

解决方案

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

这篇关于在WHERE子句中使用别名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！