如何在Where子句中使用Alias? [英] How to use Alias in Where clause?

问题描述

我有此过程:

 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会产生此错误:

but SQL Developer generate this error:

错误(20,10):PL/SQL:ORA-00904:"EXPIRED_DATE":无效的标识符

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

我相信PLSQL允许我在Where子句中使用Alias，但是我忘记了什么?

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

谢谢.

推荐答案

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

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

• 在WHERE子句中复制CASE语句

• 使用子查询:

• 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子句中使用Alias?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！