From: bob on 22 Apr 2010 12:32 "Jeroen Mostert" 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" 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: OPENQUERYNext: Need help (again) with hierarhyID