oracle CASE WHEN sql语句中缺少关键字错误 [英] missing keyword error in oracle CASE WHEN sql statement
问题描述
我正在编写一个程序,其中有sql语句,它将使用CASE WHEN
语句在TEMP_WF_WORKFLOW
表中插入值.条件是STATUS_ID
是0
时是EVENT_ID=10003
,而STATUS_ID
是1
时是EVETN_ID=10018
.当我尝试使用CASE WHEN
为此,它给我错误缺少关键字我不知道,但是如果不使用CASE WHEN
语句,还有其他方法可以做到这一点.我正在考虑使用cursor
,但不知道该怎么做.
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.
这是我的查询:
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
您混合了 CASE 语句的两种不同的语法.
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
将您的表情更改为-
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.
更新,OP说他仍然遇到缺少关键字错误.这是一个测试案例,证明它是不正确的.缺少的关键字将使用正确的CASE语句修复.
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>
这篇关于oracle CASE WHEN sql语句中缺少关键字错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!