Posted by Gert-Jan Strik on 05/29/07 17:40

Paul, try this:

UPDATE ..
SET MyCol = (
SELECT SUM(Columns_which_might_contain_null)
FROM (
SELECT CAST(DynamicCol1 AS int) AS
Columns_which_might_contain_null
UNION ALL SELECT DynamicCol2
UNION ALL SELECT DynamicCol3
) T
)

Because

SELECT SUM(Columns_which_might_contain_null)
FROM (
SELECT CAST(NULL AS int) AS
Columns_which_might_contain_null
UNION ALL SELECT NULL
UNION ALL SELECT 1
) T

SELECT SUM(Columns_which_might_contain_null)
FROM (
SELECT CAST(NULL AS int) AS
Columns_which_might_contain_null
UNION ALL SELECT NULL
UNION ALL SELECT NULL
) T


-----------
1

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET
operation.

-----------
NULL

(1 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET
operation.


Gert-Jan


info@vanoordt.nl wrote:
>
> 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

[Back to original message]