NULL value issue - 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

Transact-SQL (2000)

NULL value issue

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-12 : 15:17:06

Iam getting a warning.Is there anything I can use to eliminate this warning

Warning: Null value is eliminated by an aggregate or other SET operation.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-12 : 15:23:51

SET ANSI_WARNINGS OFF helps but will it cause anyother issues with NULL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 15:33:50

read up on the ISNULL() function in books on-line to replace NULLS with other values.

for example, replace:

SUM(Amount) As Total

with

SUM(ISNULL(Amount,0)) As Total

- Jeff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-07-17 : 16:41:49

Why is it that


SUM(Coalesce(Amount,0)) As Total

Produces this error
while


SUM(ISNULL(Amount,0)) As Total

does not?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 16:52:16

Do you have some sample code to test on?

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-17 : 17:40:14

quote:
Originally posted by Vinnie881

Why is it that

SUM(Coalesce(Amount,0)) As Total

Produces this error
while

SUM(ISNULL(Amount,0)) As Total

does not?

Wierd.. I never noticed that before.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-17 : 18:10:10

[code]DECLARE @Sample TABLE (i INT)

INSERT @Sample
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT 3

SELECT SUM(COALESCE(i, 0)) As Total1
FROM @Sample

SELECT SUM(ISNULL(i, 0)) As Total2
FROM @Sample[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources