SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Warning: Null value is eliminated by an aggregate
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dzabor
Posting Yak Master

USA
132 Posts

Posted - 09/21/2010 :  08:47:03  Show Profile  Send dzabor an AOL message  Reply with Quote
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

India
232 Posts

Posted - 09/21/2010 :  08:52:19  Show Profile  Reply with Quote
UPDATE DNN_IMIS_SECURITY_NAME
SET SECURITY_ROLE = ISNULL(security_role,some_default_value) + ',11'


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

Edited by - rohitvishwakarma on 09/21/2010 08:55:06
Go to Top of Page

rohitvishwakarma
Posting Yak Master

India
232 Posts

Posted - 09/21/2010 :  08:54:20  Show Profile  Reply with Quote
and if you don't want to consider Nulls then

Add the following to the WHERE Clause
AND SECURITY_ROLE IS NOT NULL
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 09/21/2010 :  10:22:05  Show Profile  Reply with Quote
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





Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 09/21/2010 :  10:38:09  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 09/21/2010 :  16:58:25  Show Profile  Visit jcelko's Homepage  Reply with Quote
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
Go to Top of Page

dzabor
Posting Yak Master

USA
132 Posts

Posted - 10/08/2010 :  16:32:20  Show Profile  Send dzabor an AOL message  Reply with Quote
Thanks - that worked.

dz
Go to Top of Page

mmkrishna1919
Yak Posting Veteran

India
85 Posts

Posted - 04/17/2013 :  09:31:02  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 04/17/2013 :  09:39:04  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000