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.
Paulinfo@.vanoordt.nl wrote in news:1180430739.196981.227870
@.q69g2000hsb.googlegroups.com:
Quote: Originally Posted by 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)|||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...@.vanoordt.nl wrote:
Quote: Originally Posted by 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|||On 29.05.2007 14:06, M A Srinivas wrote:
Quote: Originally Posted by On May 29, 4:37 pm, i...@.vanoordt.nl wrote: |
Quote: Originally Posted by >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|||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|||Robert Klemme <shortcutter@.googlemail.comwrote in news:5c2k0eF2tfjc8U2
@.mid.individual.net:
Quote: Originally Posted by On 29.05.2007 14:06, M A Srinivas wrote: |
Quote: Originally Posted by >On May 29, 4:37 pm, i...@.vanoordt.nl wrote: |
Quote: Originally Posted by >>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
Quote: Originally Posted by 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.
Quote: Originally Posted by 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
> |
|||>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 :)|||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:
Quote: Originally Posted by >
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 |
|||I should have written
COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) + 0*COALESCE
(Col1, Col2, Col3)|||On 29.05.2007 14:46, info@.vanoordt.nl wrote:
Quote: Originally Posted by 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|||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.|||On 31.05.2007 10:21, info@.vanoordt.nl wrote:
Quote: Originally Posted by 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|||info@.vanoordt.nl wrote:
Quote: Originally Posted by >
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|||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
