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 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…