as case when 报错,oracle CASE WHEN sql语句中缺少关键字错误

I am writing a procedure in which i have sql statement which will insert values in TEMP_WF_WORKFLOW table using CASE WHEN statement. The condition is when STATUS_ID is 0 then the EVENT_ID=10003 and when STATUS_ID is 1 then EVETN_ID=10018. When i try to use CASE WHEN for this its giving me error missing keyword.I dont know but is there any other way to do this if not using CASE WHEN statement. I am thinking about using cursor but dont know how to do this.

Here is my query:

CREATE OR REPLACE PROCEDURE ext_self_10003_sigwf AS BEGIN

-- first empty TEMP_WF_WORKFLOW table

EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_WF_WORKFLOW';

-- get WF_WORKFLOW table data

INSERT INTO temp_wf_workflow (status_id, event_id, order_number)

SELECT

wf.status_id,

CASE WHEN wf.status_id = 0 THEN event_id = 10003

WHEN wf.status_id = 1 THEN event_id = 10018 END AS eventid,

tsm.order_number

FROM wf_workflow@fonic_retail wf

JOIN tmp_soap_monitoring_ids tsm ON tsm.subscription_id = wf.subscription_id

WHERE tsm.order_type = 'SELF_REGISTRATION' AND wf.name = 'SIGNUP_MOBILE_PRE_PAID';

COMMIT;

END ext_self_10003_sigwf;

解决方案

CASE EVENT_ID WHEN WF.STATUS_ID=0 THEN EVENT_ID=10003

WHEN WF.STATUS_ID=1 THEN EVENT_ID=10018 END AS EVENTID

You have mixed two different syntax of CASE statement.

1.simple_case_statement

CASE [ expression ]

WHEN condition_1 THEN result_1

WHEN condition_2 THEN result_2

...

WHEN condition_n THEN result_n

ELSE result

END

2.searched_case_statement

CASE

WHEN expression condition_1 THEN result_1

WHEN expression condition_2 THEN result_2

...

WHEN expression condition_n THEN result_n

ELSE result

END

Change your expression to -

CASE

WHEN WF.STATUS_ID=0 THEN 10003

WHEN WF.STATUS_ID=1 THEN 10018

END AS EVENTID

Follow this link to see the documentation for both the syntax.

Update OP says he still gets the missing keyword error. This is a test case to show it is not true. The missing keyword will be fixed with correct CASE statement.

SQL> CREATE OR REPLACE

2 PROCEDURE EXT_SELF_10003_SIGWF

3 AS

4 BEGIN

5 -- first empty TEMP_WF_WORKFLOW table

6 EXECUTE IMMEDIATE 'TRUNCATE TABLE TEMP_WF_WORKFLOW';

7 -- get WF_WORKFLOW table data

8 INSERT

9 INTO TEMP_WF_WORKFLOW

10 (

11 STATUS_ID,

12 EVENT_ID,

13 ORDER_NUMBER

14 )

15 SELECT WF.STATUS_ID,

16 CASE

17 WHEN WF.STATUS_ID=0

18 THEN 10003

19 WHEN WF.STATUS_ID=1

20 THEN 10018

21 END AS EVENTID,

22 TSM.ORDER_NUMBER

23 FROM WF_WORKFLOW@FONIC_RETAIL WF

24 JOIN TMP_SOAP_MONITORING_IDS TSM

25 ON TSM.SUBSCRIPTION_ID=WF.SUBSCRIPTION_ID

26 WHERE TSM.order_type ='SELF_REGISTRATION'

27 AND WF.NAME ='SIGNUP_MOBILE_PRE_PAID';

28 COMMIT;

29 END EXT_SELF_10003_SIGWF;

30 /

Warning: Procedure created with compilation errors.

SQL> sho err

Errors for PROCEDURE EXT_SELF_10003_SIGWF:

LINE/COL ERROR

-------- --------------------------------------------------

7/3 PL/SQL: SQL Statement ignored

23/8 PL/SQL: ORA-00942: table or view does not exist

SQL>