Warning: Null value is eliminated by an aggregate or other SET operation.
If you are a programmer or database administrator, you must have come across this problem. But, this does not make a lot of difference usually. But we should be always aware of such warnings too.
This problem usually comes while using multiple joins and aggregate functions like sum(), avg(), etc in SQL Server. The main cause is due to the fact the while aggregating some data may have null. And we know that when we add, subtract, multiply or divide with null, it will return null. So, the warning alerts that the result may not be correct or aggregate function is returning null due to null value being manipulated with other numerical value.
Once in SQL, I encountered with same problem while using
Isnull(sum(e.tcd_amt),0)
in a query statement.
The reason is that e.tcd_amt is sometimes null so problem in aggregating.
So, I replaced the code with following.
sum(isnull(e.tcd_amt,0))
The code worked fine without warning as it changes the null value to 0 before summing up, so not a problem for other normal numerical values.
2 comments:
This problem comes only in the case of multiple joins in SQL statement.
It is not the case in single select, update or delete statement.
The problem does not come in following statement.
select Isnull(sum(e.tcd_amt),0)
from #tbl_final
Funny thing, but it is actually possible to get this warning with a simple UPDATE statement with no subqueries in SQL Server 2005 even if you specifically use IS NOT NULL in order to not update any null values.
Post a Comment