Re: Warning about null and open question to Oracle

In article <3rpil5$2ga_at_imp.demon.co.uk> keith_at_keithl.demon.co.uk (Keith Leng) writes:>stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) wrote:
>
>>>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.
>

"Fail" was probably the wrong word. Oracle behaves as expected. I meant that you may not get back the results that you really want.

If 0 is a valid value for a or b, then nvl(a,0) and nvl(b,0) return the same result whether the column a zero value OR is null.

So instead of using zero in the nvl function, one needs to come up with a value which is guaranteed not to exist in the column. And this could be a problem.

<snip>
>Keith Leng (keith_at_keithl.demon.co.uk)

Steve Cosner (stevec_at_zimmer.csufresno.edu) Received on Thu Jun 15 1995 - 00:00:00 CEST