CODEWITHSUNDEEP
sql-updateexistscase-when
Compile Commit
Compile Commit

Reputation: 443

Oracle Update with the Case When Exists clause

I need a trigger to update a table DIRECTORY_NUMBER when one value of DN_NUM column matches with MSISDN column value of a different table (RNPH_REQUETS_DETAILS) under a different schema(NKADM). The trigger will run every time there's a new entry in the DIRECTORY_NUMBER table. Based upon several conditions, the values of the DN_STATUS column and a few other columns need to be updated. The updated value of the DN_STATUS column will be 'r' if the conditions are met, and 'w' if the conditions are not met. Active portion of my code is given below:

UPDATE d 
  SET d.DN_STATUS = CASE WHEN EXISTS (SELECT 1 from NKADM.RNPH_REQUESTS_DETAILS n where n.MSISDN = d.DN_NUM AND n.PROCESS_STATE_ID = 4 AND n.ACTION='IN' AND n.FAILED_STATUS IS NULL AND TRUNC(n.MODIFICATION_DATE) = TRUNC(SYSDATE))
                      THEN 'r' 
                      ELSE 'w'
                      END,
    d.DN_MODDATE = SYSDATE,
    d.BUSINESS_UNIT_ID = 2,
    d.HLCODE = 5
  WHERE d.DN_ID =: NEW.DN_ID
  AND d.PLCODE = 1004
  AND d.DN_STATUS = 'f'
  FROM DIRECTORY_NUMBER d;

I am getting the following error:

Error(48,1): PL/SQL: SQL Statement ignored

Error(60,3): PL/SQL: ORA-00933: SQL command not properly ended

The errors get resolved only if I remove the references. But that gives a different result than intended. When the code is as follows:

UPDATE DIRECTORY_NUMBER 
  SET DN_STATUS     = CASE WHEN EXISTS (SELECT 1 from NKADM.RNPH_REQUESTS_DETAILS where MSISDN = DN_NUM AND PROCESS_STATE_ID = 4 
                                        AND ACTION='IN' AND FAILED_STATUS IS NULL AND TRUNC(MODIFICATION_DATE) = TRUNC(SYSDATE))
                      THEN 'r' 
                      ELSE 'w'
                      END,
    DN_MODDATE      =SYSDATE,
    BUSINESS_UNIT_ID=2,
    HLCODE          =5
  WHERE DN_ID       =:NEW.DN_ID
  AND PLCODE        =1004
  AND DN_STATUS     ='f';

  COMMIT;

Even when the CASE WHEN EXISTS condition is true (returns result when run independently), the value of DN_STATUS gets updated to 'w'.

Update: I tried with the following code:

UPDATE DIRECTORY_NUMBER
    SET DN_STATUS     = 'r',
    DN_MODDATE      =SYSDATE,
    BUSINESS_UNIT_ID=2,
    HLCODE          =5
  WHERE DN_ID       =:NEW.DN_ID
  AND PLCODE        =1004
  AND DN_STATUS     ='f';
  AND DN_NUM in (select MSISDN from NKADM.RNPH_PROCESS_DETAILS where PROCESS_STATE_ID = 4);

This isn't working either. If I remove the last condition, the resultant row has DN_STATUS value of 'f', and the MSISDN is in NKADM.RNPH_PROCESS_DETAILS table with PROCESS_STATE_ID = 4. I don't understand why it's not working.

What am I doing wrong?

Upvotes: 1

Views: 1044

Answers (1)

RGruca
RGruca

Reputation: 204

In BEFORE update/insert trigger for EACH ROW you can modify data of record which is currently processed. You don't need to call an extra UPDATE to change the data.

In other words you can do something like this

IF :NEW.PLCODE = 1004 AND :NEW.DN_STATUS = 'f' THEN

  :NEW.DN_MODDATE       := SYSDATE;
  :NEW.BUSINESS_UNIT_ID := 2;
  :NEW.HLCODE           := 5;

  -- this query you can wrap in a function and call this function
  SELECT COUNT(1) 
    INTO lv_count
    FROM NKADM.RNPH_REQUESTS_DETAILS n 
   WHERE n.MSISDN = :NEW.DN_NUM 
     AND n.PROCESS_STATE_ID = 4 
     AND n.ACTION = 'IN' 
     AND n.FAILED_STATUS IS NULL 
     AND TRUNC(n.MODIFICATION_DATE) = TRUNC(SYSDATE);

  IF lv_count > 0 THEN
    :NEW.DN_STATUS := 'r';
  ELSE
    :NEW.DN_STATUS := 'w';
  END IF;   

END IF;

Upvotes: 1

Related Questions