|
|
You're in the
sql server (alternate)
group:needed: 1 + null = 1
sql server (alternate):
info@vanoordt.nl wrote in news:1180430739.196981.227870
@q69g2000hsb.googlegroups.com: [quoted text, click to view] > 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,
[quoted text, click to view]
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 Klemme <shortcutter@googlemail.com> wrote in news:5c2k0eF2tfjc8U2
@mid.individual.net: [quoted text, click to view] > 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. [quoted text, click to view] > 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 >
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.
[quoted text, click to view]
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
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
[quoted text, click to view]
>> 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 [quoted text, click to view] 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.
Try This
select ISNULL(null,0) + 1
I should have written
COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) + 0*COALESCE (Col1, Col2, Col3)
[quoted text, click to view]
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?
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.
[quoted text, click to view]
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
[quoted text, click to view]
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, 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
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |