CODEWITHSUNDEEP
oracle-databasestored-proceduressql-updatecase
Marcus
Marcus

Reputation: 3869

PL/SQL: ORA-00936: missing expression in update query using case AND condition

I have the below query where i am using update query using case statement. I am running this query inside procedure. I am getting an error in the third line of update query as PL/SQL: ORA-00936: missing expression in update query using case statement. I want to use && condition in my first case of update query but its failing. The procedure is very big and thts why i did not mention in my question.

update
CAPTURING set LAST_TASK_ID= 
CASE WHEN (SELECT MAX(ID) from TEMP_SOAP_MONITORING AND LAST_TASK_ID ) IS NULL THEN SELECT MAX(ID) from SOAP_MONITORING@FONIC_RETAIL 
CASE WHEN (SELECT MAX(ID) from TEMP_SOAP_MONITORING) IS NULL THEN LAST_TASK_ID + 1 ELSE (SELECT MAX(ID) from TEMP_SOAP_MONITORING) END,
CAPTURING_DATE = CURRENT_TIMESTAMP, LAST_CAPTURED_DATE = LAST_CAPT_DATE where DB_TABLE='TEMP_SOAP_MONITORING';

Upvotes: 0

Views: 2359

Answers (1)

AHiggins
AHiggins

Reputation: 7227

Your conditions and checks were all tangled up: I've untangled them to do what I think you are trying to accomplish.

UPDATE CAPTURING 
SET 
    LAST_TASK_ID = CASE 
        WHEN (SELECT MAX(ID) from TEMP_SOAP_MONITORING) IS NULL AND LAST_TASK_ID IS NULL THEN (SELECT MAX(ID) FROM SOAP_MONITORING@FONIC_RETAIL)
        WHEN (SELECT MAX(ID) from TEMP_SOAP_MONITORING) IS NULL THEN LAST_TASK_ID + 1 
        ELSE (SELECT MAX(ID) from TEMP_SOAP_MONITORING) END,
    CAPTURING_DATE = CURRENT_TIMESTAMP, 
    LAST_CAPTURED_DATE = LAST_CAPT_DATE 
WHERE DB_TABLE = 'TEMP_SOAP_MONITORING';

Upvotes: 1

Related Questions