代码之家 › 专栏 › 技术社区 › Delta76

pl/sql在add运算符中不能添加2个以上的参数?

plsql database sql

Delta76 · 技术社区 · 16 年前

我有这个程序:

  PROCEDURE P_SUMMARIZE_ACTIVATED
  (
  pStartDate IN DATE,
  pEndDate IN DATE,
  SummaryCur OUT MEGAGREEN_CUR
  )
  IS
  BEGIN
    OPEN SummaryCur FOR
    SELECT USER_ID, 
           sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1,
           SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Package2,
           SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END) AS Package3,
           (Package1 + Package2 +  Package3) AS TOTAL
    FROM ALL_ADSL
    WHERE ACTIVATION_DATE BETWEEN pStartDate AND pEndDate
    group by USER_ID
    ORDER BY USER_ID;
    END;

无法执行此过程,SQL开发人员返回此错误:

错误(544,36):pl/sql:ora-00904:“package3”:无效标识符

我该怎么处理?

谢谢您。

2 回复 | 直到 16 年前

Tony Andrews 16 年前

您可以嵌套SQL以避免复制SUM和CASE表达式:

    SELECT USER_ID, 
           Package1,
           Package2,
           Package3,
           (Package1 + Package2 +  Package3) AS TOTAL
    FROM
    ( SELECT USER_ID, 
             sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1,
             SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Package2,
             SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END) AS Package3
      FROM ALL_ADSL
      WHERE ACTIVATION_DATE BETWEEN pStartDate AND pEndDate
      group by USER_ID
    )
    ORDER BY USER_ID;

或者,如果总共只有3种服务类型,则可以执行以下操作:

SELECT USER_ID, 
       sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1,
       SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Package2,
       SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END) AS Package3
       COUNT(*) AS Total
FROM ALL_ADSL
WHERE ACTIVATION_DATE BETWEEN pStartDate AND pEndDate
group by USER_ID
ORDER BY USER_ID;

或者如果有其他服务类型要排除,则

SELECT USER_ID, 
       sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1,
       SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Package2,
       SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END) AS Package3
       SUM(CASE WHEN SERVICETYPE_ID IN (1,2,3) THEN 1 ELSE 0 END) AS Total
FROM ALL_ADSL
WHERE ACTIVATION_DATE BETWEEN pStartDate AND pEndDate
group by USER_ID
ORDER BY USER_ID;

CristiC jason.zissman 16 年前

错误来自于使用3个和的别名。试着用这个来代替:

  PROCEDURE P_SUMMARIZE_ACTIVATED
  (
  pStartDate IN DATE,
  pEndDate IN DATE,
  SummaryCur OUT MEGAGREEN_CUR
  )
  IS
  BEGIN
    OPEN SummaryCur FOR
    SELECT USER_ID, 
           sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) AS Package1,
           SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) AS Package2,
           SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END) AS Package3,
           (sum(case SERVICETYPE_ID WHEN 1 THEN 1 ELSE 0 END) +
            SUM(CASE SERVICETYPE_ID WHEN 2 THEN 1 ELSE 0 END) + 
            SUM(CASE SERVICETYPE_ID WHEN 3 THEN 1 ELSE 0 END)) INTO TOTAL
    FROM ALL_ADSL
    WHERE ACTIVATION_DATE BETWEEN pStartDate AS pEndDate
    group by USER_ID
    ORDER BY USER_ID;
    END;