oracle CASE WHEN sql语句中缺少关键字错误

  • Z时代
  • 2024-01-10
  • 分类：问答

我正在编写一个程序，其中有sql语句，它将TEMP_WF_WORKFLOW使用usingCASE WHEN语句在表中插入值。条件是when

STATUS_IDis 0thenEVENT_ID=10003和when STATUS_IDis 1then

EVETN_ID=10018。当我尝试为此使用CASE WHEN它给我错误缺少关键字我不知道，但是如果不使用CASE

WHEN语句还有其他方法可以做到这一点。我正在考虑使用，cursor但不知道该怎么做。

这是我的查询：

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 [ expression ]
   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n
   ELSE result
   END

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

单击此链接以查看两种语法的文档。

OP表示他仍然 。这是一个测试案例，证明它是不正确的。缺少的关键字将使用正确的CASE语句修复。

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语句中缺少关键字错误 的全部内容， 来源链接： utcz.com/qa/416342.html

回到顶部