Reputation: 97
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
)
Upvotes: 2
Views: 1671
Reputation: 13904
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)
)
Upvotes: 0
Reputation: 15852
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.
Upvotes: 2