Cursor: Not updating null fields 2

[acct98]

I have yet another question. I can get the cursor (below) to work. However, if the field is null it will not update the record(s). Does anyone have any ideas on how to get the cursor to update the records whether it's null or not?



DECLARE
v_ssn_update discover_cc.ssn%type;
v_cardnumber_update discover_cc.cardnumber%type;
v_expire_update discover_cc.expire%type;
v_name_on_card_update discover_cc.NAME%type;
CURSOR zegato_update IS
Select c.ssn, c.cardnumber, c.expire,c.NAME
from discover_CC c , ZEG_USER_DEF_STRING z
where c.SSN = z.SSN;
BEGIN
OPEN zegato_update;
FETCH zegato_update INTO
v_ssn_update, v_cardnumber_update, v_expire_update, v_name_on_card_update;
WHILE zegato_update %FOUND LOOP
UPDATE ZEG_USER_DEF_STRING
SET credit_card = v_cardnumber_update,
expire = v_expire_update,
name_on_card = v_name_on_card_update
where SSN = v_ssn_update
AND (
(v_cardnumber_update <> credit_card) or
(v_expire_update <> expire) or
(v_name_on_card_update <>name_on_card)
);
COMMIT;
FETCH zegato_update INTO
v_ssn_update,v_cardnumber_update, v_expire_update, v_name_on_card_update;
END LOOP;
close zegato_update;
END;
/

 

[SantaMufasa]

Acct,

Nowhere in your UPDATE statement do you have a condition "...or IS NULL...", which explains why you do not get update behaviour for NULLS. Recall that if a field is NULL, then you cannot get it to respond with "=" or "<>" conditions...it must explicitly have "IS NULL" or "IS NOT NULL" conditions for proper behaviour.

Please update your condition with the appropriate "IS NULL" or "IS NOT NULL" conditions, re-run, and post your results here.

Mufasa
(aka Dave of Sandy, Utah, USA @ 14:37 (14Sep04) UTC (aka "GMT" and "Zulu"), 07:37 (14Sep04) Mountain Time)

 

[acct98]

I tried the following:

AND (
(v_cardnumber_update <> credit_card) or
(v_cardnumber_update is null) or
(v_expire_update <> expire) or
(v_expire_update is null)or
(v_name_on_card_update <>name_on_card)or
(v_name_on_card_update is null)
);

However, none of the records were updated.

 

[lewisp]

Santa is correct in his appraisal of your problem. However, you have not checked the correct side for being NULL.

I think you need to change your code to be like this:

AND (
  (Nvl(v_cardnumber_update,'Z') <> Nvl(credit_card,'Z')) or 
  (Nvl(v_expire_update,'Z') <> Nvl(expire,'Z')) or 
  (Nvl(v_name_on_card_update,'Z') <> Nvl(name_on_card,'Z')) 
     );

 

[acct98]

Thanks, I only need to use
... <> Nvl(credit_card,'Z'))
to get it to work.

It take 2.3 hours to run--Thats another issue.