PL/SQL cannot add more than 2 arguments in add operator?

Question

I have this procedure:

  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;

This procedure cannot be complied, SQL Developer returns this error:

Error(544,36): PL/SQL: ORA-00904: "PACKAGE3": invalid identifier

How can I do to fix that?

Thank you.

Answer 1

Do you happen have a PLSQL package which name is Package3?

Answer 2

The error comes from using the alias of the 3 sums. Try using this instead:

  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;

Answer 3

You can nest the SQL to avoid duplicating the SUM and CASE expressions:

    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;

Alternatively, if there are only 3 service types in total, you could do:

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;

Or if there are other service types to exclude then

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;