我有这个程序：
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;
错误(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;
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;