oracle-databaseif-statementnullcasevarchar2

Why is my null check not working in this IF statement in Oracle?


This should be a simple question. I want a particular column claimedby to alternate between a user ID and nothing. So, I'm writing a simple procedure that should look at the column to see if a user ID is in there. If it is, set it to nothing. If it is nothing, then set it to the user ID. That's all I need. But, I'm running into difficulties with determining if the column is nothing. I'm trying various ways, such as 1) case claimedby when null, 2) case claimedby when '', 3) if length(claimedby) < 1. In none of the cases is the column being set to the incoming parameter userID. It's always remaining null. Here's my code:

create or replace PROCEDURE claimprocedure
   ( userID IN varchar2, docketnum IN varchar2 )
IS
   claimed_by varchar2(10);

  cursor c1 is
    SELECT claimedby FROM DR_RULE24.CISR_INTAKE_ERRORS_MASTER 
     WHERE docket_num = docketnum;
     
BEGIN

  open c1;
  fetch c1 into claimed_by;
  close c1;

 DBMS_OUTPUT.PUT_LINE('claimed_by = ' || claimed_by);

CASE claimed_by
    when null then update DR_RULE24.cisr_intake_errors_master set claimedby = userID where docket_num = docketnum;
    when '' then update DR_RULE24.cisr_intake_errors_master set claimedby = userID where docket_num = docketnum;
    ELSE update DR_RULE24.cisr_intake_errors_master set claimedby = '' where docket_num = docketnum;
  END CASE;
  
  if length(claimed_by) < 1 then update DR_RULE24.cisr_intake_errors_master set claimedby = userID where docket_num = docketnum;
    else update DR_RULE24.cisr_intake_errors_master set claimedby = '' where docket_num = docketnum;
   end if;

  if claimed_by = '' then update DR_RULE24.cisr_intake_errors_master set claimedby = userID where docket_num = docketnum;
  else update DR_RULE24.cisr_intake_errors_master set claimedby = '' where docket_num = docketnum;
 end if;

if claimed_by is null then update DR_RULE24.cisr_intake_errors_master set claimedby = userID where docket_num = docketnum;
  else update DR_RULE24.cisr_intake_errors_master set claimedby = '' where docket_num = docketnum;
 end if;
   
END;

I confirm the column is set to null, then I call it with:

SET SERVEROUTPUT ON
call claimprocedure('shone','106712');

The column for docket number 106712 is not being set to 'shone'. I've confirmed that the claimed_by variable in the procedure matches the structure of the claimedby column, which is varchar2(10);

I thought that maybe the IF statements might be canceling each other out. So, I commented out all the ELSE clauses and tried again. It's still null, proving that none of these IF statements are working.

It must be something simple. What am I missing? Thank you.


Solution

  • The case statement (and its cousin the case expression) comes in two forms: the "simple" form and the "searched" form.

    The simple form looks like case <expr> when <val1> then ... when <val2> then ... else ... end - this is simple, easy to read, but it only works for direct equality checks. The searched form looks like case when <condition1> then ... when <condition2> then ... else ... end. Here you write a bit more code, but this form is much more general.

    Now to your question. You are using the simple form. Its meaning is equivalent to case when claimed_by = null then ..... Can you spot the trouble here? If you can't, read again about null, and when something (anything) is ever equal to null.

    You must use the searched form, and write your condition in the correct, SQL way: case when claimed_by IS null then ....