IF condition containing IN operator inside ORACLE Trigger is not working-CodePudding

I have a trigger that uses IF condition with IN operator and two variables v_audit_user and v_evdnse_user inside IN. Both variables are containing comma separated ID values. The trigger gets compiled successfully with no errors. I am not understanding why the IF condition with IN is not working. When I select the function that assigns value to the variables independently, I do see the comma separated values, so nothing is wrong with function (see screenshot).

create or replace TRIGGER TRG_CHK_HRCQA_CASE_ACTIONS
AFTER INSERT ON KDD_CASE_ACTIONS
FOR EACH ROW
DECLARE

 user_audit kdd_review_owner.OWNER_ID%TYPE; /* The user that is displayed in audit */
 user_evdnse kdd_review_owner.OWNER_ID%TYPE; /* The user that took action in evidence tab */
 v_audit_user NUMBER;  /* The HRCO/QA user from audit tab */
 v_evdnse_user NUMBER; /* The HRCO/QA user from evidence tab */
 LV_ERRORCODE VARCHAR2(1000);

BEGIN

  /* pass the username into the variables */

    SELECT kro.OWNER_ID into user_audit from kdd_review_owner kro where kro.OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

    SELECT kro.OWNER_ID into user_evdnse from kdd_review_owner kro where kro.OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

  /* fetch the comma separated IDs */
  
   v_audit_user := F_GET_HRCQA_ACTIONS(:NEW.CASE_INTRL_ID,user_audit,'AUDIT');

   v_evdnse_user := F_GET_HRCQA_ACTIONS(:NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE');


  --  select ENTITY_ID into v_evdnse_user from table(f_get_arg_table(F_GET_HRCQA_ACTIONS(:NEW.CASE_INTRL_ID,user_evdnse,'EVDNSE')));
  /* If the action taken is by QA or HRCO role */

    IF (:NEW.ACTION_SEQ_ID in (v_audit_user,v_evdnse_user)) 

    THEN

  /* then insert record in the SC_HRCQA_CASE_ACTIONS table with IS_HRCO_QA flag as Y  */

        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'Y');

--        ELSE
--
--  /* else the logged in user is NOT HRCO/QA hence insert record in the SC_HRCQA_CASE_ACTIONS table with IS_HRCO_QA flag as N  */   
--
--        Insert into SC_HRCQA_CASE_ACTIONS (ACTION_SEQ_ID,ACTION_BY_ID,ACTION_TS,STATUS_CD,CASE_INTRL_ID,ACTION_ID,NEW_CASE_OWNR_ASSGN_ID,CASE_DUE_TS,PREV_CASE_OWNR_ASSGN_ID,IS_HRCO_QA) 
--        values (:NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID, :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'N');

    END IF;

EXCEPTION
    WHEN OTHERS THEN LV_ERRORCODE := SQLCODE;
        INSERT INTO KDD_LOGS_MSGS (LOG_DT, LOG_INFO_TX, REMARK_TX)
        VALUES (SYSDATE,'ErrorCode - ' || LV_ERRORCODE,'TRG_CHK_HRCQA_CASE_ACTIONS');
END;

CodePudding user response：

I don't have your tables so I'll try to illustrate it using my own code.

This is what you're doing now: to us (humans), it is obvious that L_NEW_ID (its value is 10) is contained in L_VAR1 (its value is '10,20'). However, Oracle doesn't recognize that and returns "Not OK":

SQL> declare
  2    -- does L_NEW_ID exist in L_VAR1 and L_VAR2 (using your code)?
  3    l_new_id number := 10;
  4    --
  5    l_var1 varchar2(20) := '10,20';
  6    l_var2 varchar2(20) := '30,40';
  7  begin
  8    if l_new_id in (l_var1, l_var2) then
  9       dbms_output.put_line('OK');
 10    else
 11       dbms_output.put_line('Not OK');
 12    end if;
 13  end;
 14  /
Not OK

PL/SQL procedure successfully completed.

SQL>

So, what to do? One option is to split variables to rows and then check whether search value (10, right?) exists in such a list of values (rows). The result is - as you can see - "OK":

SQL> declare
  2    l_new_id number := 10;
  3    --
  4    l_var1 varchar2(20) := '10,20';
  5    l_var2 varchar2(20) := '30,40';
  6    l_cnt  number;
  7  begin
  8    -- count how many times L_NEW_ID exists in L_VAR1 nad L_VAR2
  9    select count(*)
 10    into l_cnt
 11    from (-- split L_VAR1 into rows
 12          select regexp_substr(l_var1, '[^,] ', 1, level) val
 13            from dual
 14            connect by level <= regexp_count(l_var1, ',')   1
 15          union all
 16          -- split L_VAR2 into rows
 17          select regexp_substr(l_var2, '[^,] ', 1, level)
 18            from dual
 19            connect by level <= regexp_count(l_var2, ',')   1
 20         ) x
 21    where x.val = l_new_id;
 22
 23    if l_cnt > 0 then
 24       dbms_output.put_line('OK');
 25    else
 26       dbms_output.put_line('Not OK');
 27    end if;
 28  end;
 29  /
OK

PL/SQL procedure successfully completed.

SQL>

Just to verify it, let's change L_NEW_ID value to e.g. 99 and see what happens; as expected, "Not OK" as 99 isn't contained in 10, 20 nor 30, 40.

SQL> l2
  2*   l_new_id number := 10;
SQL> c/10/99
  2*   l_new_id number := 99;
SQL> /
Not OK

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

You cannot pass a comma-delimited string stored in a single variable to an IN condition and expect it to be parsed as multiple values as it is not.

If you want to use a single variable containing a delimited list then you will need to use string functions to find a sub-string match:

create or replace TRIGGER TRG_CHK_HRCQA_CASE_ACTIONS
  AFTER INSERT ON KDD_CASE_ACTIONS
  FOR EACH ROW
DECLARE
  v_owner_id    kdd_review_owner.OWNER_ID%TYPE;
  v_audit_user  VARCHAR2(1000);
  v_evdnse_user VARCHAR2(1000);
  LV_ERRORCODE  VARCHAR2(1000);
BEGIN
  SELECT OWNER_ID
  into   v_owner_id                         -- You only need one variable here
  from   kdd_review_owner
  where  OWNER_SEQ_ID = :NEW.ACTION_BY_ID;

  v_audit_user := F_GET_HRCQA_ACTIONS(:NEW.CASE_INTRL_ID, v_owner_id, 'AUDIT');
  v_evdnse_user := F_GET_HRCQA_ACTIONS(:NEW.CASE_INTRL_ID, v_owner_id, 'EVDNSE');

  IF ','||v_audit_user||','||v_evdnse_user||',' LIKE '%,'||:NEW.ACTION_SEQ_ID||',%'
  THEN
     Insert into SC_HRCQA_CASE_ACTIONS (
       ACTION_SEQ_ID, ACTION_BY_ID, ACTION_TS, STATUS_CD, CASE_INTRL_ID,
       ACTION_ID, NEW_CASE_OWNR_ASSGN_ID, CASE_DUE_TS, PREV_CASE_OWNR_ASSGN_ID, IS_HRCO_QA
     ) values (
       :NEW.ACTION_SEQ_ID, :NEW.ACTION_BY_ID, :NEW.ACTION_TS, :NEW.STATUS_CD, :NEW.CASE_INTRL_ID,
       :NEW.ACTION_ID, :NEW.NEW_CASE_OWNR_ASSGN_ID, :NEW.CASE_DUE_TS, :NEW.PREV_CASE_OWNR_ASSGN_ID,'Y'
     );
  END IF;
EXCEPTION
    WHEN OTHERS THEN LV_ERRORCODE := SQLCODE;
        INSERT INTO KDD_LOGS_MSGS (LOG_DT, LOG_INFO_TX, REMARK_TX)
        VALUES (SYSDATE,'ErrorCode - ' || LV_ERRORCODE,'TRG_CHK_HRCQA_CASE_ACTIONS');
END;
/