?
Solved

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

Posted on 2005-03-04
7
Medium Priority
?
37,662 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
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

571 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