Prev: separte text
Next: Preferred file group
From: DWalker07 on 26 Feb 2010 13:03
Given that this query is easy to understand:

Select case 'Rep'
when null
then 'SBr'
else 'Rep'
end

.... it returns "Rep", as we expect. (These are internal codes for us.)

Why is this one unusual?

Declare @VA As Char(1)

Select
case @VA
when null then
case 'Rep'
when null
then 'SBr'
else 'Rep'
end
else
@VA
end

.... I expect this to also return "Rep" but it doesn't. Since @VA is
certainly null, I want the middle part to be run:

case 'Rep'
when null
then 'SBr'
else 'Rep'
end

.... and return "Rep". Can someone please explain this to me?

Of course, it's the same as this, removing the variable:

Select
case null
when null then
case 'Rep'
when null
then 'SBr'
else 'Rep'
end
else
null
end

... which I also expect to return "Rep". We're on SQL 2005 but 2000 and
2008 do the same thing (so I suspect this is how it's supposed to work,
but dang if I can see why).

Thanks.

David Walker
From: Bob Barrows on 26 Feb 2010 13:13
DWalker07 wrote:
> Given that this query is easy to understand:
>
> Select case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
>
> ... it returns "Rep", as we expect. (These are internal codes for
> us.)
>
> Why is this one unusual?
>
> Declare @VA As Char(1)
>
> Select
> case @VA
> when null then
> case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
> else
> @VA
> end
>
> ... I expect this to also return "Rep" but it doesn't.

Errr ... 'Rep' is not null, it's a literal string containing the
characters Rep

--
HTH,
Bob Barrows


From: Scott Morris on 26 Feb 2010 13:19

"DWalker07" <none(a)none.com> wrote in message
news:%23AMSN4wtKHA.5940(a)TK2MSFTNGP02.phx.gbl...
> Given that this query is easy to understand:
>
> Select case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
>
> ... it returns "Rep", as we expect. (These are internal codes for us.)
>
> Why is this one unusual?
>
> Declare @VA As Char(1)
>
> Select
> case @VA
> when null then
> case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
> else
> @VA
> end
>
> ... I expect this to also return "Rep" but it doesn't. Since @VA is
> certainly null, I want the middle part to be run:
>
> case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
>
> ... and return "Rep". Can someone please explain this to me?
>
> Of course, it's the same as this, removing the variable:
>
> Select
> case null
> when null then
> case 'Rep'
> when null
> then 'SBr'
> else 'Rep'
> end
> else
> null
> end
>
> .. which I also expect to return "Rep". We're on SQL 2005 but 2000 and
> 2008 do the same thing (so I suspect this is how it's supposed to work,
> but dang if I can see why).
>
> Thanks.
>
> David Walker


From: Plamen Ratchev on 26 Feb 2010 13:23
You cannot check for NULL with simple CASE expression. This is because it gets translated to searched CASE expression
evaluating the value with = NULL. For example:

CASE X WHEN NULL THEN 0 ELSE 1 END

This gets expanded to:

CASE WHEN X = NULL THEN 0 ELSE 1 END

Since nothing equals NULL (even another NULL, so NULL = NULL cannot evaluate to true), the WHEN clause evaluates to false.

The correct way to check for NULLs is to use searched CASE expression (or replace NULL with COALESCE):

CASE WHEN X IS NULL THEN 0 ELSE 1 END

--
Plamen Ratchev
http://www.SQLStudio.com
From: DWalker07 on 26 Feb 2010 13:26
"Bob Barrows" <reb01501(a)NOyahoo.SPAMcom> wrote in news:uax7x9wtKHA.5940
@TK2MSFTNGP02.phx.gbl:

> DWalker07 wrote:
>> Given that this query is easy to understand:
>>
>> Select case 'Rep'
>> when null
>> then 'SBr'
>> else 'Rep'
>> end
>>
>> ... it returns "Rep", as we expect. (These are internal codes for
>> us.)
>>
>> Why is this one unusual?
>>
>> Declare @VA As Char(1)
>>
>> Select
>> case @VA
>> when null then
>> case 'Rep'
>> when null
>> then 'SBr'
>> else 'Rep'
>> end
>> else
>> @VA
>> end
>>
>> ... I expect this to also return "Rep" but it doesn't.
>
> Errr ... 'Rep' is not null, it's a literal string containing the
> characters Rep
>

No, actually that's not the reason: I know that 'Rep' is not null; hence
I expected the "else 'Rep'" clause to get executed and reuturn the word
"rep".

I just figured it out: The issue is that null does not equal null. The
"searched case statement" uses = for its comparisons.

I should have remembered that, but I usually use the other form of the
Case statement, where you can say "Case When <column> Is Null Then ..."
and that one works fine with nulls.

Thanks.

David Walker
 |  Next  |  Last
Pages: 1 2
Prev: separte text
Next: Preferred file group