Posted by Robert Klemme on 05/29/07 12:18

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. 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]