Prev: separte text
Next: Preferred file group
From: TheSQLGuru on 27 Feb 2010 21:56
Good ol' tri-valued logic! NULLs SUCK! :-)

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"DWalker07" <none(a)none.com> wrote in message
news:eEHGFFxtKHA.4908(a)TK2MSFTNGP06.phx.gbl...
> "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


First  |  Prev  | 
Pages: 1 2
Prev: separte text
Next: Preferred file group