Posted by Chris.Cheney on 05/29/07 12:47

Robert Klemme <shortcutter@googlemail.com> wrote in news:5c2k0eF2tfjc8U2
@mid.individual.net:

> On 29.05.2007 14:06, M A Srinivas wrote:
>> On May 29, 4:37 pm, i...@vanoordt.nl wrote:
>>> Using coalesce is the same sort of solution as using isnull. It
>>> doesn't behave as my requirements state. In particular, the result
>>> will be 0 if all inputs are null. It is required that the result be
>>> null.
>>> Thanks anyway.
>>
>> No. Did you test
>>
>> Result will be null if all are null .
>> since
>> COALESCE(Col1, Col2, Col3) returns null and
>> 0 + 0 + 0 + null is null
>> COALESCE takes more arguments and ISNULL only two
>>
>> declare @a table (col1 int,col2 int,col3 int)
>>
>> insert into @a values (1,null,null)
>> insert into @a values (null,2,null)
>> insert into @a values (null,null,3)
>> insert into @a values (1,2,null)
>> insert into @a values (null,2,3)
>> insert into @a values (1,null,3)
>> insert into @a values (null,null,null)
>>
>> select COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) +
>> COALESCE(Col1,
>> Col2, Col3) from @a
>>
>>
>> 2
>> 4
>> 6
>> 4
>> 7
>> 5
>> NULL
>
> Now there is only the small issue that one of the column values is
added
> twice - and you do not know which one.

Oops yes! Sorry. Must put brain in gear before letting fingers loose on
keyboard. Thanks for picking this up.

> Something like this is probably
> better:
>
> -- untested
> SELECT CASE
> WHEN COALESCE(col1, col2, col2) IS NULL
> THEN NULL
> ELSE
> COALESCE(col1, 0) +
> COALESCE(col2, 0) +
> COALESCE(col3, 0)
> END
> ...
>
> Kind regards
>
> robert
>

[Back to original message]