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.
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 ....