Prev: OPENQUERY
Next: Need help (again) with hierarhyID
From: bob on 22 Apr 2010 12:32

"Jeroen Mostert" <jmostert(a)xs4all.nl> wrote in message
news:4bcfd74d$0$22920$e4fe514c(a)news.xs4all.nl...
> On 2010-04-21 22:52, Q wrote:
>> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
>
> Wow, I don't know how I managed to miss that.
>
> --
> J.

Are you sure that 'IS NULL' works with CASE/WHEN? I know that it does with
WHERE, etc. but with CASE it seems to work without 'IS' only.
So, maybe you did not miss anything afterall :)


From: Jeroen Mostert on 22 Apr 2010 15:24
On 2010-04-22 18:32, bob wrote:
> "Jeroen Mostert"<jmostert(a)xs4all.nl> wrote in message
> news:4bcfd74d$0$22920$e4fe514c(a)news.xs4all.nl...
>> On 2010-04-21 22:52, Q wrote:
>>> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
>>
>> Wow, I don't know how I managed to miss that.
>>
> Are you sure that 'IS NULL' works with CASE/WHEN? I know that it does with
> WHERE, etc. but with CASE it seems to work without 'IS' only.
> So, maybe you did not miss anything afterall :)
>
>
CASE has two forms, the first for matching a single expression against
multiple others, like so:

CASE x
WHEN A THEN x_A
WHEN B THEN x_B
...
END

This form definitely does not work for NULL, or rather, the WHEN clauses
behave as if you had written an equality comparison out in full. You can
easily test this yourself: setting ANSI_NULLS will affect the result just
like in (in)equality comparisons (if ANSI_NULLS is OFF, A = NULL will be
true, the intuitive but wrong result).

The other form evaluates multiple boolean expressions, like so:

CASE
WHEN x = A THEN x_A
WHEN x = B THEN x_B
...
END

The expressions obey the regular rules and thus require IS NULL if you want
to test for NULL. In fact, this is the only form you can use if you want to
test for NULL in a CASE.

--
J.
First  |  Prev  | 
Pages: 1 2
Prev: OPENQUERY
Next: Need help (again) with hierarhyID