Impetus
The impetus for this blog comes from a recent discussion that happened in one of the forums of which I’m a member.
There was a question asked by one of the fellow members regarding an error he was getting while using an aggregate function with CASE expression. This post explains the reason for the error and also gives you an alternate method which you can use for avoiding the error.
There was a question asked by one of the fellow members regarding an error he was getting while using an aggregate function with CASE expression. This post explains the reason for the error and also gives you an alternate method which you can use for avoiding the error.
Scenario
The sample code for the issue looks like the below
If Object_ID(‘tempdb..#Test’,’U’) Is Not Null Drop Table #Test;
Create Table #Test(ID Int identity(1,1),Qty Numeric(15,2));
Insert
Into #Test (Qty)
Values (100.25),
(94.34);
Declare @Factor Int=0;
Select Case When @Factor=0 Then 0 Else Sum(Qty/@Factor) End
From #Test;
If you execute the above code you will see that you get the error as below in the SQL Management Studio
Msg 8134, Level 16, State 1, Line 11
Divide by zero error encountered.
If you think about it you would expect the statement to work correctly as you’re already checking for the error condition inside CASE expression and returning a default value for it. Then why does it error?
Explanation
The reason for the above noticed behavior is well documented in books online.
The CASE statement evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible. Aggregate expressions that appear in WHEN arguments to a CASE statement are evaluated first, then provided to the CASE statement.
As you see from above it clearly suggests that while aggregate functions are used in CASE expression they get evaluated first and causes the divide by zero error as seen in the above case.
The full documentation can be seen from the below link
Solution
If you want to avoid the error then you need to go for an approach as below
If Object_ID(‘tempdb..#Test’,’U’) Is Not Null Drop Table #Test;
Create Table #Test
(
ID Int identity(1,1),
Qty Numeric(15,2)
);
Insert
Into #Test (Qty)
Values (100.25),
(94.34);
Declare @Factor Int=0;
Select Coalesce(Sum(Qty/Nullif(@Factor,0)) ,0)
From #Test;
which would make sure the conversion of 0 in the denominator happens before the actual division operation
Conclusion
As you see from the above illustration the CASE ..WHEN expression tends to evaluate aggregations first before applying conditional logic. So in that case we would need to go for an alternative approach to avoid causing error conditions as illustrated above.
Thanks to Geri Reshef for bringing this up as a discussion in the forums.
Thanks to Geri Reshef for bringing this up as a discussion in the forums.
