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?

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

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.

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 …