NULL + 1000 = ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

NULL + 1000 = ?

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-28 : 18:49:11

HI , i have this

select Sum(field1) , sum(field2) from blablabla

i get

field1 Field2
NULL 1000

But if i do this ( Instead comma i put + )

select Sum(field1) + sum(field2) from blablabla

I get NULL, why ?

i would like to get 1000.

tks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 18:56:25

Have a look at the CONCAT_NULL_YIELDS_NULL setting in SQL Server Books Online.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 19:07:46

Actually, since these are probably integer columns, you'll want to use ISNULL. The setting that I mentioned is for concatenating of character columns not integer columns. So this is what you'll need to do:

SELECT ISNULL(Column1, 0) + ISNULL(Column2, 0)
FROM Table1

Tara

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-28 : 19:09:03

Even using SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

i still get NULL instead 1000.

SET CONCAT_NULL_YIELDS_NULL { ON | OFF }

works fine only when concatenate String not to add values.

tks anyway.

I am still looking for a solution.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-04-28 : 19:11:55

Please see my last post. You were probably typing your reply as I added another post.



SET NOCOUNT ON

CREATE TABLE Table1
(
Column1 INT NULL,
Column2 INT NULL
)

INSERT INTO Table1 VALUES(1, 4)
INSERT INTO Table1 VALUES(3, NULL)
INSERT INTO Table1 VALUES(4, 4)
INSERT INTO Table1 VALUES(NULL, 4)

SELECT Column1 + Column2 AS SumOfColumns
FROM Table1

SELECT ISNULL(Column1, 0) + ISNULL(Column2, 0) AS SumOfColumns
FROM Table1

DROP TABLE Table1

Tara

CLages
Posting Yak Master

116 Posts

Posted - 2004-04-28 : 19:12:10

Using ISNULL works fine

tks

Clages

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources