decode in an update statement 1

[lel75]

Can you please tell me if I am using decode correctly in this update statement? I am trying to compare a new and an old value in a trigger code and if the old value is not null and the new value is null, I would like for the value inserted into the table to be blank and not the value 'NULL'. If the new student_id is not null, then to just insert the real value.

ELSE
IF old.student_flag = 'ACTIVE') AND new.student_flag = 'ACTIVE') THEN
IF NVLold.student_id, 'NULL') <> NVLnew.student_id, 'NULL') THEN
UPDATE test.test_student
SET student_id = DECODEnew.student_id, 'NULL', '', :new.student_id)
WHERE student_num = ld.student_num;

Thanks for your help in advance!

 

[SantaMufasa]

Lel,

Your &quot;IF&quot; statement [&quot;IF NVLold.student_id, 'NULL')...&quot;] is actually a very clever way to determine if &quot;ld.student_id&quot; is different from &quot;:new.student_id&quot;, but your DECODE statement makes me believe that you misunderstand the nature and use of NULL.

Your DECODE statement as it is coded presently is not checking if :new.student_id is NULL; it is checking to see if it contains the four characters &quot;N-U-L-L&quot;; and if it does contain those four characters, the DECODE inserts the zero-length absence of value, '', which is identical to the zero-length absence of value, NULL.

I cannot see any evidence that :new.student_id ever contains the four-character string &quot;N-U-L-L&quot;, nor does your code do what you specified in your original thread specification, &quot;... I would like for the value inserted into the table to be blank and not the value 'NULL'...&quot; Your code is inserting a zero-length absence of value. And lastly, I don't believe what you really want in your data is a &quot;blank space&quot;, either. In the Oracle world, use of a &quot;blank space character&quot; (a one-byte ASCII value 32) to represent the absence of value is a &quot;No-no&quot;.

So, even though your use of DECODE is syntactically okay, I believe you don't want or need the DECODE. You will be fine (in Oracle terms) to just say,
&quot;...UPDATE test.test_student
SET student_id = :new.student_id
WHERE student_num = ld.student_num;&quot;

I'm guessing that you will have a response to my assertions above.

Cheers,

Dave
Sandy, Utah, USA @ 00:09 GMT, 17:09 Mountain Time

 

[lel75]

Dave,

Thank you for the prompt reply. This site is great! Yes, your response is exactly what I'm looking for. I was confused with the DECODE and now that you've explained it, things are alot clearer. Again thanks for the excellent support.

lel75