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

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.

On May 29, 4:37 pm, i... (AT) vanoordt (DOT) nl wrote:
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

On 29.05.2007 14:06, M A Srinivas wrote:
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

Thanks for your reactions,
There is this problem with Srinivas' solution and the solution Robert
supplies is actually what I already proposed myself. Namely separating
the case where all columns are null from those cases where some ar not
null, and this is very slow. (I'm talking about hundreds of columns
and millions of rows.)
I was actually thinking more of a solution to ignore the nulls, rather
than on the fly setting them to 0.
More suggestions are appreciated.
Regards,
Paul

Update your entire database once. Add a non-null constraint to the
columns. This is a "mop the floor, and fix the leak" philosophy.

Kill the moron who screwed up the schema, so he cannot do this
again. This is preventative maintenance

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 (AT) vanoordt (DOT) nl wrote:

On 29.05.2007 14:46, info (AT) vanoordt (DOT) nl wrote:
That sounds scary. Who in heck invents a schema with /hundreds/ of
numeric columns? Does this make sense at all?

robert

Try This

select ISNULL(null,0) + 1

I like Chris' last idea:
COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) +
0*COALESCE(Col1, Col2, Col3)

This calculates the value in one expression. I expect it to perform
well, at least not much worse than without the last term.

Gert-Jan, I need some time to find out what your code does. With all
respect, it lacks the simplicity of the above solution.

Robert, it does make sense and the schema is build dynamically.

Thanks for your responses.