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子句中引用列别名-您的选择是：


复制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;