Warning: Null value is eliminated by an aggregate

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

General SQL Server Forums

New to SQL Server Programming

Warning: Null value is eliminated by an aggregate

Author

Topic

dzabor
Posting Yak Master

138 Posts

Posted - 2010-09-21 : 08:47:03

I run a script and it works fine, but when I place into a job as a stored procedure it throws the following error:
"Warning: Null value is eliminated by an aggregate or other SET operation."

UPDATE DNN_IMIS_SECURITY_NAME
SET SECURITY_ROLE = security_role + ',11'
FROM DNN_IMIS_SECURITY_NAME D
JOIN
(
SELECT ACTIVITY.ID, Max(ACTIVITY.THRU_DATE) AS MaxDate
FROM ACTIVITY
WHERE ACTIVITY.ACTIVITY_TYPE = 'SUB'
GROUP BY ACTIVITY.ID
) M
ON D.ID = M.ID
WHERE M.MaxDate <= DATEADD(mm, +3, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
and M.MaxDate >= GetDate()
AND SECURITY_ROLE Not LIKE '%11%'
and SECURITY_ROLE LIKE '%,5%'
or M.MaxDate <= DATEADD(mm, +3, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
and M.MaxDate >= GetDate()
and SECURITY_ROLE LIKE '5,%'
AND SECURITY_ROLE Not LIKE '%11%'
or M.MaxDate <= DATEADD(mm, +3, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP))
and M.MaxDate >= GetDate()
and SECURITY_ROLE = '5'
AND SECURITY_ROLE Not LIKE '%11%'

How can I set this to run and allow the nulls?

Thanks,
dz

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 08:52:19

UPDATE DNN_IMIS_SECURITY_NAME
SET SECURITY_ROLE = ISNULL(security_role,some_default_value) + ',11'

ex: SET SECURITY_ROLE = ISNULL(security_role,'0') + ',11'

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-21 : 08:54:20

and if you don't want to consider Nulls then

Add the following to the WHERE Clause
AND SECURITY_ROLE IS NOT NULL

X002548
Not Just a Number

15586 Posts

Posted - 2010-09-21 : 10:22:05

use COALESCE

Be as ANSI as possible

MOO

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

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-09-21 : 10:38:09

It's not an error, it's just a warning message. You can suppress it by running SET ANSI_WARNINGS OFF before your query if you absolutely have to.

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-09-21 : 16:58:25

quote:
Originally posted by dzabor

I run a script and it works fine, but when I place into a job as a stored procedure it throws the following error:
"Warning: Null value is eliminated by an aggregate or other SET operation."

It is a warning, not an exception. You can keep running if that makes sense for your business rules.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

dzabor
Posting Yak Master

138 Posts

Posted - 2010-10-08 : 16:32:20

Thanks - that worked.

dz

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2013-04-17 : 09:31:02

Hi All,

I too facing the same issue in my complex proc.How Can i check which line causing this issues?

Thanks

M.MURALI kRISHNA

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 09:39:04

quote:
Originally posted by mmkrishna1919

Hi All,

I too facing the same issue in my complex proc.How Can i check which line causing this issues?

Thanks

M.MURALI kRISHNA

Simply set ANSI_WARNINGS OFF

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources