CASE NOT equal

I have the following statement...
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
ELSE
'Equal'
END
The value for SRV.srv_package is NULL and the value for
PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
When they are clearly NOT equal. Am I failing to use CASE incorrectly
here? Can I NOT use <>?
Any help would be GREATLY appreciated...
wnfisbaNo, nulls do not come under anything. So, usually nulls are not predictable.
add this to the first line and try
SET ANSI_NULLS OFF
and try it..
if it still doesn't work
then do this... hope this helps.
CASE
WHEN isnull(SRV.srv_package,0) <> isnull(PkgtoSRV.Package_SRV,0)
THEN 'Not Equal'
ELSE
'Equal'
END
"wnfisba" wrote:

> I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query return...Equal?
'
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba|||NULL is unknown. Repeating something I posted yesterday:
<snip>
Nothing will ever = NULL, since the definition of NULL is unknown.
Think about it this way, if I have a form with a field that says "gender"
and I forget to check either male or female, can you say with any certainty
that I am:
(a) male?
(b) female?
(c) not male?
(d) not female?
Further, can you say with any certainty that someone else, who also forgot
to specify their gender, is:
(a) the same gender as me?
(b) the opposite gender from me?
(c) not the same gender as me?
(d) not the opposite gender from me?
</snip>
So, in order to do this comparison, you either need to account for NULLs in
the ELSE, or use COALESCE to allow bogus values into the comparison.
CASE
WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
THEN 'Not Equal'
WHEN SRV.srv_package = PkgtoSRV.Package_SRV
THEN 'Equal'
ELSE 'Unknown - one or both are NULL'
END
-- assuming -1 is not a possible value
-- though, I don't even have an idea what data type you are using
CASE WHEN COALESCE(SRV.srv_package, -1) = COALESCE(PkgtoSRV.Package_SRV, -1)
THEN 'Equal'
ELSE 'Not Equal'
END
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:E25E0D4E-0113-401A-A07C-FB7B86C7DC96@.microsoft.com...
>I have the following statement...
> CASE
> WHEN SRV.srv_package <> PkgtoSRV.Package_SRV
> THEN 'Not Equal'
> ELSE
> 'Equal'
> END
> The value for SRV.srv_package is NULL and the value for
> PkgtoSRV.Package_SRV is 2006-05-05. So why does this query
> return...Equal?
> When they are clearly NOT equal. Am I failing to use CASE incorrectly
> here? Can I NOT use <>?
> Any help would be GREATLY appreciated...
> wnfisba