在WHERE子句中使用别名

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

我有一个查询，用于显示表a中最近更新不够的任何行。(每一行在“月号”后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

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子句中的最后一行导致“ORA-00904无效标识符”错误。不用说，我不想在WHERE子句中重复整个解码函数。任何想法吗?(修正和变通都被接受…)

5 个解决方案

#1

101

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

这是不可能的，因为按时间顺序，在SELECT之前发生的地方，总是执行链中的最后一步。

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

不应该出现性能影响。在应用外部条件之前，Oracle不需要实现内部查询——Oracle将考虑在内部转换此查询，并将谓词推入内部查询，如果这样做是划算的，那么将这样做。——贾斯汀洞穴

#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 .identifier = B.identifier
HAVING MONTH_NO > UPD_DATE

#3

Or you can have your alias in a HAVING clause

或者可以在have子句中使用别名

#4

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;

#5

It's possible to effectively define a variable that can be used in both the SELECT, WHERE and other clauses.

可以有效地定义可以在SELECT、WHERE和其他子句中使用的变量。

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.

感谢Syed Mehroz Alam。

#1

101