Re: SQL IF

"Greg" <actiris_at_loxinfo.co.th> wrote in message news:394480BC.B77FBF04_at_loxinfo.co.th...
> If I have the SQL code
>
> if (variable1 <> variable2) then....
>
> Works if the Variables have a value
> but if one of them is null, it doesn't.
>
> how to include the case one of the variables are null in the statement.
>
> thanks Greg

Nulls are treated as "un-evaluatable" (my word), meaning that Oracle can't really tell if one is equal to the other or not when one or more is null because nulls are "unknowns".

You can NVL them (cheating and not 100% reliable because you are hard-coding values):

if nvl(var1,'x') != nvl(var2,'x') then

(But if var1 or var2 actually *are* legitimately 'x', then that's where it's really not reliable...) I guess you can use that if the value you are using you know will never be a valid value anyway (in terms of business operations). It's still kinda cheating though.

My preference:

if (var1 is not null
and var2 is not null
and var1 != var2) or
(var1 is not null and
var2 is null) or
(var1 is null and
var2 is not null) then...

Longer, but better coding.

-Matt Received on Mon Jun 12 2000 - 00:00:00 CEST