2

I want to protect the following statement from a divide by zero error. How can I achieve this?

SELECT [MyColumn] = sum( 
   case when v.Process_FundingDt 
   between @begDt and @endDt  
   and v.Loan_Purpose_Desc = 'PURCHASE' 
   then v.Loan_LoanAmt 
   else 0 
   end
 ) / sum (
   case when v.Process_FundingDt 
   between @begDt and @endDt 
   then v.Loan_LoanAmt else 0 
   end
 )
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rick Walker
  • 97
  • 7
  • Use a `WHERE EXISTS` to see if the denominator will be non-zero - if it will not, do not select that row to begin with. – Patashu Apr 29 '13 at 22:54
  • 2
    Well, setting a default value of zero in your denominator is a sure way to get a divide by zero error. See this post: http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql – Volvox Apr 29 '13 at 23:10
  • Thanks guys. I added NULL instead of zero and for now, it works. Until the Business Units complain that they want to see a zero. Then its back to the drawing board. But at least for now my boss is happy. – Rick Walker Apr 29 '13 at 23:23
  • By the way, I followed post 38 that was linked before posting this question. I couldn't get the statement to evaluate without a syntax error in my situation. – Rick Walker Apr 29 '13 at 23:28

2 Answers2

2

This may help:

select [MyColumn] = case when Denominator = 0 then NULL else Numerator / Denominator end
  from (
   select sum( 
   case when v.Process_FundingDt between @begDt and @endDt and v.Loan_Purpose_Desc = 'PURCHASE' then v.Loan_LoanAmt 
     else 0 end ) as Numerator,
   sum (
   case when v.Process_FundingDt between @begDt and @endDt then v.Loan_LoanAmt
     else 0 end ) as Denominator
   ) as Edgar

It probably works better with your missing FROM clause added.

Of course, it may run afoul of this feature, depending on what the query optimizer does.

HABO
  • 15,314
  • 5
  • 39
  • 57
0

you could use the NULLIF method mentioned here: how to avoid divide by zero error

SELECT [MyColumn] = sum( 
   case when v.Process_FundingDt 
   between @begDt and @endDt  
   and v.Loan_Purpose_Desc = 'PURCHASE' 
   then v.Loan_LoanAmt 
   else 0 
   end
 ) / NULLIF(sum (
   case when v.Process_FundingDt 
   between @begDt and @endDt 
   then v.Loan_LoanAmt else 0 
   end, 0)
 )
Community
  • 1
  • 1
Henrik Staun Poulsen
  • 13,154
  • 4
  • 23
  • 26