CODEWITHSUNDEEP
sqlsql-servert-sqlcasedivide
user3580480
user3580480

Reputation: 482

Divide by zero error encountered, but should be excluded by CASE

I have the following code and I am getting an error:

Divide by zero error encountered.

SELECT
CASE WHEN SUM([monthly_qty]) = 0 THEN 999 
ELSE ROUND(SUM([monthly_buy] * ([monthly_markup]+100)/100),2) * SUM([monthly_qty] / [monthly_qty]) END as [monthly_total]
FROM [xxxxx].[dbo].[quote_items] WHERE docid='10152'

The field that is causing the error is the second [monthly_qty], just before the END of the CASE statement.

SUM([monthly_qty] / **[monthly_qty]**)

The value of monthly_qty is zero, so the error makes sense, but I am confused as this field is inside the CASE statement, so the expected result is 999

Any help greatly appreciated.

Upvotes: 0

Views: 87

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270091

The reason is simple. This code:

SUM([monthly_qty]) = 0

Does not prevent a divide-by-zero in this code:

SUM([monthly_qty] / [monthly_qty])

The expressions are different.

The simplest solution (and essentially the "standard" approach) is to use NULLIF():

SUM([monthly_qty] / NULLIF([monthly_qty], 0))

However, I wonder if you really intend:

SUM([monthly_qty]) / NULLIF(SUM([monthly_qty]), 0)

Upvotes: 0

Hamed Rashno
Hamed Rashno

Reputation: 344

I can't understand reason of some parts of your code like this:

SUM([monthly_qty] / [monthly_qty])

The result is error when [monthly_qty] = Zero, And 1 for non zero.

Anyway, You can set a default value When "monthly_qty" is zero:

IIF([monthly_qty]= 0,'YOUR_DEFAULT_VALUE', [monthly_qty] / [monthly_qty])

Then:

SUM(IIF([monthly_qty]= 0,'YOUR_DEFAULT_VALUE', [monthly_qty] / [monthly_qty]))

Upvotes: 3

Mikey Bronowski
Mikey Bronowski

Reputation: 61

Have a read at MS Docs, there is an example of your case under Remarks.

The CASE expression evaluates its conditions sequentially and stops with the first condition whose condition is satisfied. In some situations, an expression is evaluated before a CASE expression 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 expression are evaluated first, then provided to the CASE expression. For example, the following query produces a divide by zero error when producing the value of the MAX aggregate. This occurs prior to evaluating the CASE expression.

Upvotes: 2

Related Questions