Posted by Chris.Cheney on 05/29/07 10:16

info@vanoordt.nl wrote in news:1180430739.196981.227870
@q69g2000hsb.googlegroups.com:

> Hi,
>
> I need this behaviour: 1 + null = 1
> I have a (dynamic) set of many columns containing decimals that I want
> to add as follows:
> if all columns are null the result should be null
> if not all columns are null, the null columns may be regarded as 0.
>
> E.g.
> null + null + 1 = 1
> null + null + null = null
>
> The problem is that the first expression yields null.
>
> Up till now I generated an update statement with isnull(<column>,0),
> however, then the second expression yields 0.
> I can add another update statment setting the result to null if all
> columns are null, but this is very slow, and not very intuitive
> either.
> How nice it would be if there were a setting like 'concat null yields
> null' for arithmetic operators.
>
> Anyone any idea how to fix this?
>
> Thanks.
> Paul
>
>

COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) + COALESCE(Col1,
Col2, Col3)

• Next in forum: Re: ANN: SQL Monitor Version 2 has just been released
• Prev in forum: Transpose column into rows
• Thread view: Re: needed: 1 + null = 1

[Reply to this message]