sql – 如何在Where子句中使用Alias？

我有这个程序：

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;

但SQL Developer生成此错误：

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

我相信PLSQL允许我在Where子句中使用Alias,但有什么我忘了吗？

提前致谢.

解决方法

>在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;

Oracle 9i

WITH summary AS (
  SELECT s.account_name,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.expired_date
     FROM summary x
    WHERE x.expired_date - CURRENT_DATE < pDayDiff;

（编辑：ASP站长网）