Re: comparing data with NULL values

In article <7gl3li$m3u$1_at_nnrp1.dejanews.com>,  <lionroar_at_my-dejanews.com> wrote:
>I'm having trouble comparing fields stored in variables if any of the
>variables has a NULL value. Basically when I say:
>
>IF X = Y THEN
> Z;
>END IF;
>
>... if one of the 2 values contains a NULL value and the other doesn't, then
>the condition passes and Z is processed.... but the 2 values are NOT equal ..
>one is NULL?? If anyone can help can you send me an email at
>brian_connor_at_gmacm.com

Welcome to 3-value logic: True, False, and Null. "Unknown" is sometimes substituted for "Null." I don't think your conditional statement is exactly like you showed us. It is probably like this:   If X <> Y then
    do_something;
  Else
    do_Z;
  End if;

Do_Z gets executed when the X<>Y statement is either False, or Null: The X<>Y statement is false when both have non-null values, and are not equal. The X<>Y condition has a null (or unknown) result if either X or Y is null and also if both X and Y are null.

If any value used in a condition can be null, place an NVL function around it:
  If NVL(X,' ')<>NVL(Y,' ') then...
But you always have to invent some impossible value to go between the quotes. Such fun!

The 3-value logic mess is the worst thing about PL/SQL programming.

Steve Cosner Received on Mon May 03 1999 - 23:11:15 CDT