Warning: Null value - 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

Old Forums

CLOSED - General SQL Server

Warning: Null value

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2005-06-03 : 11:33:32

Whe I run a scrip in QL , I am getting the message:

Warning: Null value is eliminated by an aggregate or other SET operation.
What this message mean ?
What shoould I do if i dont want to see this message?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-03 : 11:52:50

you have ansi warnings on and an aggregate (sum, max, min, ...) on a column which contains a null value.

You can set ansi_warnings off but better to remove the nulls

e.g.
sum(coalesce(col,0))

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-03 : 11:54:15

wouldn't that distort if you are using avg()??

Corey

Secret Service Agent: Mr. President, you're urinating on me.
President Lyndon Johnson: I know I am. It's my prerogative.

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-03 : 12:19:12

Yes


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 int)
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, Null UNION ALL
SELECT 2, 3
Go

SELECT Col1, SUM(Col2), COUNT(Col2), AVG(Col2)
FROM myTable99
GROUP BY Col1

SELECT Col1, SUM(COALESCE(Col2,0)), COUNT(COALESCE(Col2,0)), AVG(COALESCE(Col2,0))
FROM myTable99
GROUP BY Col1

SELECT Col1, SUM(Col2), COUNT(Col2), AVG(Col2)
FROM myTable99
WHERE Col2 IS NOT NULL
GROUP BY Col1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-06-03 : 19:57:25

Depending on your business rules, just ignore the warning.

HTH

=================================================================
All restraints upon man's natural liberty, not necessary for the simple maintenance of justice, are of the nature of slavery, and differ from each other only in degree. -Lysander Spooner, lawyer (1808-1887)

Kristen
Test

22859 Posts

Posted - 2005-06-05 : 02:57:31

"Depending on your business rules, just ignore the warning"

Tensd to muck up applications because of the way it is expressed to them through ADo.

I prefer to exclude the NULL values if at all possible.

SELECT COUNT(MyColumn)
FROM MyTable
WHERE MyColumn IS NOT NULL

or even

SELECT SUM(CASE WHEN MyColumn IS NULL THEN 0 ELSE 1),
... some other aggregates ...
FROM MyTable

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources