Procedure Not Compiling Insert with Case 1

kvang · Sep 8, 2008

I am trying to compile this procedure in Oracle 8i:

Code:

CREATE OR REPLACE PROCEDURE Sp_Update_LSAPP_WORKFLOWSTATUS IS
BEGIN
	DELETE FROM LSAPP_WORKFLOWSTATUS;
	--
	INSERT INTO LSAPP_WORKFLOWSTATUS
	SELECT DEPARTMENT, DEPTINITIAL, 
	SUM(CASE WHEN CATG = 'BACKLOG' THEN 1 ELSE 0 END) AS WOB_BACKLOG,
	SUM(CASE WHEN CATG = 'BACKLOG' AND CREATETIME < TRUNC(SYSDATE-(SELECT VAR_VALUE FROM LSAPP_VARIABLES WHERE VAR_NAME = 'old_backlog')) THEN 1 ELSE 0 END) AS OLD_BACKLOG,
	SUM(CASE WHEN CATG = 'BACKLOG' AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%')) THEN 1 ELSE 0 END) AS WOB_BACKLOG_COMPLETE,
	CASE WHEN SUM(CASE WHEN CATG = 'BACKLOG' THEN 1 ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CATG = 'BACKLOG' AND CREATETIME < TRUNC(SYSDATE-(SELECT VAR_VALUE FROM LSAPP_VARIABLES WHERE VAR_NAME = 'old_backlog')) THEN 1 ELSE 0 END) / SUM(CASE WHEN CATG = 'BACKLOG' THEN 1 ELSE 0 END) END AS OLD_BACKLOG_PCT,    
	SUM(CASE WHEN CATG = 'NEW' THEN 1 ELSE 0 END) AS WOB_NEW, 
	SUM(CASE WHEN CATG = 'NEW' AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%')) THEN 1 ELSE 0 END) AS WOB_NEW_COMPLETE,
	CASE WHEN SUM(CASE WHEN CATG = 'NEW' THEN 1 ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN CATG = 'NEW' AND EXISTS (SELECT * FROM REPORTLS.RETAIL_PROCESSLOG C WHERE C.WOBNUM = F_WOBNUM AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%')) THEN 1 ELSE 0 END) / SUM(CASE WHEN CATG = 'NEW' THEN 1 ELSE 0 END) END AS NEW_COMPLETE_PCT,
	SYSDATE AS LASTUPDATE
	FROM ( 
	SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, 'BACKLOG' AS CATG, CREATETIME 
	FROM LS_WOBBACKLOG A, REPORTLS.WORKTYPES B 
	WHERE A.WORKTYPE = B.WORKTYPE 
	AND ENQUEUETIME < TRUNC(SYSDATE-1)+17/24 
	UNION ALL 
	SELECT WOBNUM AS F_WOBNUM, DEPARTMENT, DEPTINITIAL, 'NEW' AS CATG, STARTTIME 
	FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B 
	WHERE A.WORKTYPE = B.WORKTYPE  
	AND ENQUEUETIME >= TRUNC(SYSDATE-1)+17/24 AND ENQUEUETIME < TRUNC(SYSDATE)+17/24 
	AND DEPARTMENT NOT LIKE 'Direct Retail Lending%' 
	AND USERID NOT IN (SELECT P8USERID FROM REPORTLS.BGPERFORMERS) 
	AND STEPNAME IN (SELECT STEPNAME FROM REPORTLS.LSAPP_STEPNAME) ) 
	GROUP BY DEPARTMENT, DEPTINITIAL;
	COMMIT;
	EXCEPTION 
	WHEN OTHERS THEN
	ROLLBACK; -- rollback the transaction
END;
/

Getting the following error: PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

Help please.

LKBrwnDBA · Sep 8, 2008

CASE is not supported in 8i PL/SQL. [3eyes]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

SantaMufasa · Sep 8, 2008

Bkou,

First of all, I highly recommend that when you create SQL code, use well-structured documentation standards that allow readers to easily interpret the sections and subsections (queries and sub-queries) in your code.

Second, since all of your code in the PL/SQL procedure is pure, standard SQL, debug the SQL code before you attempt to embed it into a PL/SQL block.

So, could you please run the following SELECT statement that I have simply visually reformatted from your original code:

Code:

        SELECT DEPARTMENT
              ,DEPTINITIAL
              ,SUM(CASE WHEN CATG = 'BACKLOG'
                        THEN 1 
                        ELSE 0 
                        END) AS WOB_BACKLOG
              ,SUM(CASE WHEN CATG = 'BACKLOG' 
                         AND CREATETIME < TRUNC(SYSDATE-(SELECT VAR_VALUE
                                                           FROM LSAPP_VARIABLES
                                                          WHERE VAR_NAME = 'old_backlog'
                                                        )
                                               )
                        THEN 1
                        ELSE 0 
                        END) AS OLD_BACKLOG
              ,SUM(CASE WHEN CATG = 'BACKLOG'
                         AND EXISTS (SELECT *
                                       FROM REPORTLS.RETAIL_PROCESSLOG C
                                      WHERE C.WOBNUM = F_WOBNUM
                                        AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%')
                                    )
                        THEN 1
                        ELSE 0
                        END) AS WOB_BACKLOG_COMPLETE
              ,CASE WHEN SUM(CASE WHEN CATG = 'BACKLOG'
                             THEN 1
                             ELSE 0
                             END) = 0
                    THEN 0
                    ELSE SUM(CASE WHEN CATG = 'BACKLOG'
                                   AND CREATETIME < TRUNC(SYSDATE-(SELECT VAR_VALUE
                                                                     FROM LSAPP_VARIABLES
                                                                    WHERE VAR_NAME = 'old_backlog'
                                                                  )
                                                         )
                                  THEN 1
                                  ELSE 0 
                                  END) / 
                         SUM(CASE WHEN CATG = 'BACKLOG'
                                  THEN 1
                                  ELSE 0 
                                  END)
                    END AS OLD_BACKLOG_PCT
              ,SUM(CASE WHEN CATG = 'NEW'
                        THEN 1
                        ELSE 0 
                        END) AS WOB_NEW
              ,SUM(CASE WHEN CATG = 'NEW'
                         AND EXISTS (SELECT *
                                       FROM REPORTLS.RETAIL_PROCESSLOG C
                                      WHERE C.WOBNUM = F_WOBNUM
                                        AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%'))
                        THEN 1
                        ELSE 0
                        END) AS WOB_NEW_COMPLETE
              ,CASE WHEN SUM(CASE WHEN CATG = 'NEW'
                                  THEN 1
                                  ELSE 0
                                  END) = 0
                    THEN 0
                    ELSE SUM(CASE WHEN CATG = 'NEW'
                                   AND EXISTS (SELECT *
                                                 FROM REPORTLS.RETAIL_PROCESSLOG C
                                                WHERE C.WOBNUM = F_WOBNUM
                                                  AND (C.STEPNAME = 'Complete' OR C.STEPNAME LIKE '%QA%'))
                                  THEN 1
                                  ELSE 0
                                  END) /
                         SUM(CASE WHEN CATG = 'NEW'
                             THEN 1
                             ELSE 0
                             END)
                    END AS NEW_COMPLETE_PCT
              ,SYSDATE AS LASTUPDATE
          FROM (SELECT WOBNUM AS F_WOBNUM
                      ,DEPARTMENT
                      ,DEPTINITIAL
                      ,'BACKLOG' AS CATG
                      ,CREATETIME 
                  FROM LS_WOBBACKLOG A, REPORTLS.WORKTYPES B 
                 WHERE A.WORKTYPE = B.WORKTYPE 
                   AND ENQUEUETIME < TRUNC(SYSDATE-1)+17/24 
                UNION ALL 
                SELECT WOBNUM AS F_WOBNUM
                      ,DEPARTMENT
                      ,DEPTINITIAL
                      ,'NEW' AS CATG
                      ,STARTTIME 
                  FROM REPORTLS.RETAIL_PROCESSLOG A, REPORTLS.WORKTYPES B 
                 WHERE A.WORKTYPE = B.WORKTYPE  
                   AND ENQUEUETIME >= TRUNC(SYSDATE-1)+17/24 
                   AND ENQUEUETIME < TRUNC(SYSDATE)+17/24 
                   AND DEPARTMENT NOT LIKE 'Direct Retail Lending%' 
                   AND USERID NOT IN (SELECT P8USERID
                                        FROM REPORTLS.BGPERFORMERS) 
                   AND STEPNAME   IN (SELECT STEPNAME
                                        FROM REPORTLS.LSAPP_STEPNAME)
               ) 
         GROUP BY DEPARTMENT, DEPTINITIAL;

Let us know if the above works. If you still receive an error, then you know it was not a result of running in PL/SQL.

Please post your findings here.

[santa]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

Dagon · Sep 9, 2008

I have a feeling CASE isn't supported inside PL/SQL in Oracle 8. It was one of the peculiar differences there were between ordinary SQL and SQL inside PL/SQL.

kvang · Sep 9, 2008

Yeah, CASE isn't supported inside PL/SQL in Oracle 8.

BJCooperIT · Sep 9, 2008

An additional observation, a SELECT inside of a PL/SQL block must have an INTO clause (unless in a cursor).

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

SantaMufasa · Sep 9, 2008

And just in case <grin> the "casual Tek-Tips reader" does not catch the nuances of the above observations, I'd like to emphasize that while the Oracle CASE statement was not supported in Oracle 8's PL/SQL, Oracle's CASE statement is supported in the PL/SQL of Oracle 9i and beyond:

Code:

begin
    case when 1 = 1 then dbms_output.put_line ('1 = 1');
         else dbms_output.put_line ('1 <> 1');
         end case;
end;
/

1 = 1

PL/SQL procedure successfully completed.

Also, as Barb correctly mentions:

BJCooper said:
a SELECT inside of a PL/SQL block must have an INTO clause (unless in a cursor)

...this applies to independent SELECT statements in a PL/SQL block, but does not apply when a SELECT statement is dependent (i.e., within another SQL statement within a PL/SQL block), which is the situation with BKOU's code. Following is a variant of BKOU's original code. This variant shows successful execution (within an Oracle 9i PL/SQL environment) illustrating that:[ul][li]A dependent SELECT statement does not use INTO, and[/li][li]The CASE statement works just fine in PL/SQL:[/li]

Code:

SQL> select * from bkou1;

      COL1 COL2
---------- ----------
        10
        20

2 rows selected.

SQL> select * from bkou2;

no rows selected

SQL> begin
  2      insert into bkou2
  3          select col1
  4                ,case when rownum = 1 then 'Row 1'
  5                      else 'Row > 1'
  6                      end
  7            from bkou1;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from bkou2; 

      COL1 COL2
---------- ----------
        10 Row 1
        20 Row > 1

2 rows selected.

[/ul]

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

BJCooperIT · Sep 9, 2008

Sorry Dave, you are correct. Just another example of your statement about needing to "use well-structured documentation standards". These old eyes didn't pickup the INSERT statement before the SELECT.

Indented code is a wonderful thing. [pc3]

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Procedure Not Compiling Insert with Case 1

kvang

Programmer

LKBrwnDBA

MIS

SantaMufasa

Technical User

Dagon

MIS

kvang

Programmer

BJCooperIT

Programmer

SantaMufasa

Technical User

BJCooperIT

Programmer

Similar threads

Part and Inventory Search

Sponsor