在 WHERE 子句中使用别名

2021-11-26 00:00:00 sql alias decode oracle ora-00904

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

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

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

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

推荐答案

这不是直接可能的，因为按时间顺序，WHERE 发生在 before SELECT 之前，它总是执行链中的最后一步.

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:

应该不会影响性能.Oracle 不需要物化应用外部之前的内部查询条件——甲骨文会考虑在内部转换此查询并将谓词下推到内部查询，如果有成本就会这样做有效的.– 贾斯汀洞穴

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

相关文章