Re: Warning about null and open question to Oracle

From: DRathbun <drathbun_at_aol.com>
Date: 1995/06/16
Message-ID: <3rs2ph$eq5_at_newsbf02.news.aol.com>#1/1


In article <3rpil5$2ga_at_imp.demon.co.uk>, keith_at_keithl.demon.co.uk (Keith Leng) writes:

>>>IF nvl(a,0) = nvl(b,0) then
>>> do something;
>>>END IF;
 
>>Watch out! The above example will fail when a=0 and b is null, and
>>when a is null and b=0.
>
>Could you expand on that a little? I cannot see why it would fail. It
>seems to me to be equivalent to the following which works as you would
>expect.
>
>SQL> create table temp (a char(1) null, b char(1) null);
>
>Table created.
>
>SQL> insert into temp values (null,0);
>
>1 row created.
>
>SQL> select * from temp
> 2 where nvl(a,0) = nvl(b,0);
>
>A B
>- -
> 0
>
>

The original problem was to find out where a column in 'B' had been updated and therefore would be different from a column in 'A'. In this example, using NVL(col1,0) where zero is a valid value that may occur in the data, NVL(A.col1,0) = NVL(B.col1,0) should actually be NVL(A.col1,0) <> NVL(B.col1,0)... as in my original reply to this thread, which follows:

<< begin include >>

If NVL(col1,'XXX') <> NVL(col2,'XXX') then < update >;

Replace the token 'XXX' with something that should never occur in your data, and you should be set. Hope that this helps!

<< end include >>

Incidentally, I have used the CHR function to test this with some success. For example, many of the ASCII characters are not available for data entry, although they may appear in binary (ie LONG) data. If all of the data should be valid (typeable) ASCII characters, then replace the 'XXX' in my previous example with the following:

IF NVL(A.col1, chr(13)) <> NVL(B.col1,chr(13)) then < update >;

CHR(13) is, if memory serves, the ASCII representation of a carriage return, which should never occur in normal text or alphanumeric data. Hope this helps!

Regards,

Dave Rathbun
Integra Solutions
Dallas, TX
DRathbun_at_AOL.COM Received on Fri Jun 16 1995 - 00:00:00 CEST

Original text of this message