needed: 1 + null = 1

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(,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

Re: needed: 1 + null = 1

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(,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)

Re: needed: 1 + null = 1

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.

Re: needed: 1 + null = 1

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

Re: needed: 1 + null = 1

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

Re: needed: 1 + null = 1

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

Re: needed: 1 + null = 1

Robert Klemme 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
>

Re: needed: 1 + null = 1

>> I was actually thinking more of a solution to ignore the nulls, rather than on the fly setting them to 0. <<

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 :)

Re: needed: 1 + null = 1

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(,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

Re: needed: 1 + null = 1 (Correction)

I should have written

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

Re: needed: 1 + null = 1

On 29.05.2007 14:46, info@vanoordt.nl wrote:
> 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.)

That sounds scary. Who in heck invents a schema with /hundreds/ of
numeric columns? Does this make sense at all?

robert

Re: needed: 1 + null = 1

Try This

select ISNULL(null,0) + 1

Re: needed: 1 + null = 1 (Correction)

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.

Re: needed: 1 + null = 1 (Correction)

On 31.05.2007 10:21, info@vanoordt.nl wrote:
> Robert, it does make sense and the schema is build dynamically.

If you say so... To me this rather sounds like a case for

CREATE TABLE PARAMETERS (
item INT NOT NULL,
parameter_name VARCHAR(20) NOT NULL,
parameter_value INT NOT NULL,
PRIMARY KEY (
item,
parameter_name
)
)

Of course I don't know all the details...

Kind regards

robert

Re: needed: 1 + null = 1 (Correction)

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

The code assumes that you did not properly normalize your table. It
assumes that Col1, Col2 and Col3 basically have the same meaning, and
should have been modelled as three rows. So the query is transposes the
three columns to three rows. Then the standard behavior of the SUM
aggregate is used, in which means NULLs are skipped. The result will
always be a scalar, and the SUM of an empty set is NULL.

Gert-Jan

Re: needed: 1 + null = 1 (Correction)

Gert Jan, you have a point; sum() exactly does what is required.
Actually this is what I am investigating also, but it is a decision
with more implications.
Regards, Paul