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 15 28199 in**@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(<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:
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:
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
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 <sh*********@googlemail.comwrote 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
>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 in**@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
I should have written
COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) + 0*COALESCE
(Col1, Col2, Col3)
On 29.05.2007 14:46, in**@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
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, in**@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 in**@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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jamal |
last post by:
I am working on binary files of struct ACTIONS
I have a recursive qsort/mergesort hybrid that
1) i'm not a 100% sure works correctly
2) would like to convert to iteration
Any comments or...
|
by: ooze |
last post by:
typedef unsigned long PARAM;
typedef PARAM SAP;
typedef struct qnode
{
struct qnode *next;
struct qnode *prev;
}
QNODE;
|
by: Jason Curl |
last post by:
I've been reading this newsgroup for some time and now I am thoroughly
confused over what NULL means.
I've read a NULL pointer is zero (or zero typecast as a void pointer),
others say it's...
|
by: Federico Balbi |
last post by:
Hi,
I was wondering if PGSQL has a function similar to binary_checksum() of
MS SQL Server 2000. It is pretty handy when it comes to compare rows of
data instead of having to write long boolean...
|
by: Rouben Rostamian |
last post by:
Umfpack is a C library for computations dealing with sparse
matrices. Several examples in the User's Guide use a certain
cast that puzzles me. Here is an example.
The prototype of the function...
|
by: mast2as |
last post by:
Hi everyone,
I am trying to implement some specs which specify that an array of
parameter is passed to a function as a pointer to an array terminated
by a NULL chatacter. That seemed fairly easy...
|
by: Shum |
last post by:
Hi all!!! I found this piece of code from one of the threads here, I too want to get the list of tables and data types of columns, but when i tried to use this code in my program it gives me error:...
|
by: valentin tihomirov |
last post by:
As explained in "Using pointers vs. references"
http://groups.google.ee/group/borland.public.delphi.objectpascal/browse_thread/thread/683c30f161fc1e9c/ab294c7b02e8faca#ab294c7b02e8faca ,
the...
|
by: Java script Dude |
last post by:
Small glitch (IMHO) in JavaScript is related to isNaN() boolean
function. If passed a null, it returns true which is incorrect.
This one cause me quite a bit of grief until I detected it and was...
|
by: atencorps |
last post by:
Hello
I have the following code but need some help on it. The idea of the code is the main sections ie Service Management are viewable when the page is loaded and by clicking on the main...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |