?
Solved

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

Posted on 2005-03-04
7
Medium Priority
?
37,652 Views
Last Modified: 2012-08-14
I keep getting this "warning" when I run certain scripts:

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


It's not a "fatal" error, so the script still runs, but it does kick out a message to my users, which I need to find a way around.

Here's an example of one of the scripts that gerenates this warning.


      insert into #cte select [description], userfield1 as [sku], userfield2 as [cust] from series where scenarioid = @lm and (userfield1 <> '' or userfield2 <> '')
      delete from #cte where exists (select sku, customer from series sr where sr.sku = #cte.sku and sr.customer = #cte.cust and scenarioid=5960)
      delete from #cte where exists (select sku, customer from series sr left join observations ob on sr.seriesid = ob.seriesid where sr.sku = #cte.sku and sr.customer = #cte.cust and sr.scenarioid = @lm and sr.levelnum <> 0 group by sku, customer having sum(ob.uservalue01)>0)


The warning occurs as a result of the line:

delete from #cte where exists (select sku, customer from series sr left join observations ob on sr.seriesid = ob.seriesid where sr.sku = #cte.sku and sr.customer =


It has something to do with nulls being in the dataset that's getting summed... but I don't know how to account for the nulls so that I don't get this message. Any ideas?



WATYF
0
Comment
Question by:WATYF
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 4

Accepted Solution

by:
BaldProgrammer earned 1200 total points
ID: 13461028
Can ob.uservalue01 be null?  Try changing it to:

having sum(COALESCE(ob.uservalue01, 0.0) > 0)

0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13461106
replace sum(ob.uservalue01)>0 with sum(IsNull(ob.uservalue01, 0)) > 0
0
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 13461113
sry bald...didn't refresh.
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 27

Expert Comment

by:ptjcb
ID: 13461221
If you just want to kill the warning

SET ANSI_WARNINGS OFF

and at the end of the script

SET ANSI_WARNINGS ON
0
 
LVL 11

Expert Comment

by:ram2098
ID: 13461502
you write it like this....

delete from #cte where exists (select sku, customer from series sr left join observations ob on sr.seriesid = ob.seriesid where sr.sku = #cte.sku and sr.customer = #cte.cust and sr.scenarioid = @lm and sr.levelnum <> 0 group by sku, customer having sum(ISNULL(ob.uservalue01,0))>0)
0
 
LVL 11

Author Comment

by:WATYF
ID: 13462062
thanks... that was what I was looking for.


For anyone who stumbles across this in the future... the "IsNull" solution worked as well. They're pretty much doing the same thing.



WATYF
0
 
LVL 4

Expert Comment

by:BaldProgrammer
ID: 13462084
Also a FYI,

ISNull would probably have been a better solution, but out of habit I use COALESCE since it allows you to add a bunch of other 'checks'.  ISNull is either this or that.  COALESCE can have as many parameters as you want and it keeps going until it finds the first NON-NULL column.  Such that:

COALESCE(col1, col2, col3, col4, '')

Keep in mind you can get columns from other tables to use as well.  

Thanks for the points :).
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question