oracle 如何在 Where 子句中使用别名？

How to use Alias in Where clause?

提问by Quan Mai

I have this procedure:

我有这个程序：

 PROCEDURE P_LOAD_EXPIRED_ACCOUNT
  (
  pDayDiff IN NUMBER,
  ExpiredCur OUT MEGAGREEN_CUR
  )
  IS
  BEGIN
  OPEN ExpiredCur FOR
  SELECT 
  ACCOUNT_NAME, SERVICE_TYPE, 
      CASE 
      WHEN SERVICE_TYPE = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
      WHEN SERVICE_TYPE = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
      WHEN SERVICE_TYPE = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
       END 
       AS EXPIRED_DATE
  FROM SUBSCRIBERS
  WHERE (EXPIRED_DATE - CURRENT_DATE) < pDayDiff;
  END;

but SQL Developer generate this error:

但 SQL Developer 生成此错误：

Error(20,10): PL/SQL: ORA-00904: "EXPIRED_DATE": invalid identifier

错误（20,10）：PL/SQL：ORA-00904：“EXPIRED_DATE”：标识符无效

I believe that PLSQL allows me to use Alias in Where clause, but is there something I forgot?

我相信 PLSQL 允许我在 Where 子句中使用别名，但是有什么我忘记了吗？

Thanks in advance.

提前致谢。

回答by OMG Ponies

You can't reference the column alias in the WHERE clause - your options are:

您不能在 WHERE 子句中引用列别名 - 您的选项是：

• replicate the CASE statement in the WHERE clause

• use a subquery:

  PROCEDURE P_LOAD_EXPIRED_ACCOUNT(pDayDiff NUMBER,
                                   ExpiredCur OUT MEGAGREEN_CUR)
  IS
  BEGIN
  
  
     OPEN ExpiredCur FOR
     SELECT x.account_name,
            x.service_type, 
            x.expired_date
       FROM (SELECT s.account_name,
                    s.service_type, 
                    CASE 
                       WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
                       WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
                       WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
                    END AS EXPIRED_DATE
               FROM SUBSCRIBERS s) x
      WHERE x.expired_date - CURRENT_DATE < pDayDiff;
  
  
  END;
  

  Oracle 9i+

  WITH summary AS (
    SELECT s.account_name,
           s.service_type, 
           CASE 
              WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
              WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
              WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
           END AS EXPIRED_DATE
      FROM SUBSCRIBERS s)
     SELECT x.account_name,
            x.service_type, 
            x.expired_date
       FROM summary x
      WHERE x.expired_date - CURRENT_DATE < pDayDiff;
  

• 在 WHERE 子句中复制 CASE 语句

• 使用子查询：

  PROCEDURE P_LOAD_EXPIRED_ACCOUNT(pDayDiff NUMBER,
                                   ExpiredCur OUT MEGAGREEN_CUR)
  IS
  BEGIN
  
  
     OPEN ExpiredCur FOR
     SELECT x.account_name,
            x.service_type, 
            x.expired_date
       FROM (SELECT s.account_name,
                    s.service_type, 
                    CASE 
                       WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
                       WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
                       WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
                    END AS EXPIRED_DATE
               FROM SUBSCRIBERS s) x
      WHERE x.expired_date - CURRENT_DATE < pDayDiff;
  
  
  END;
  

  甲骨文 9i+

  WITH summary AS (
    SELECT s.account_name,
           s.service_type, 
           CASE 
              WHEN s.service_type = 1 THEN ADD_MONTHS(ACTIVATED_DATE,3)
              WHEN s.service_type = 2 THEN ADD_MONTHS(ACTIVATED_DATE,6)
              WHEN s.service_type = 3 THEN ADD_MONTHS(ACTIVATED_DATE,12)        
           END AS EXPIRED_DATE
      FROM SUBSCRIBERS s)
     SELECT x.account_name,
            x.service_type, 
            x.expired_date
       FROM summary x
      WHERE x.expired_date - CURRENT_DATE < pDayDiff;