Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
375 views
in Technique[技术] by (71.8m points)

missing keyword error in oracle CASE WHEN sql statement

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;
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

insert in THEN only value

CASE WHEN WF.STATUS_ID=0 THEN 10003 
WHEN WF.STATUS_ID=1 THEN 10018 END

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...