Posted by Erland Sommarskog on 10/02/05 11:49

zod91@yahoo.com (zod91@yahoo.com) writes:
> I don't understand why I get the error "Server: Msg 8134, Level 16,
> State 1, Line 1
> Divide by zero error encountered."
>
> I check for 0, actually if I change the statement after ELSE to 2, it
> will run with no issue and get 1 since the when statement is 0 in this
> case. Please help.
>
> SELECT
> CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12)* CONVERT(INT,
> ICFPM_USER_14))= 0 THEN 1
> ELSE SUM(REV_ORDER_QTY/(CONVERT(INT, ICFPM_USER_12)*CONVERT(INT,
> ICFPM_USER_14)))
> END
> FROM SOFOD, ICFPM
> WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

I guess that you get the error, because SQL Server computes both SUMs
when traversing the rows. If it were to do the statement literaly,
it would first have to traverse the rows to compute the first sum,
and if that sum is not 0, traverse once more. So you would need:

SELECT CASE WHEN SUM(CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)) = 0 THEN 1
ELSE SUM(REV_ORDER_QTY /
CASE WHEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14) <> 0
THEN CONVERT(INT, ICFPM_USER_12) *
CONVERT(INT, ICFPM_USER_14)
ELSE 1 -- or whatever
END
FROM SOFOD, ICFPM
WHERE SOFOD.PART_ID = ICFPM.PART_ID AND SOFOD.SO_ID = '105706'

This still looks funny to me. As soon as any of the columns is 0 for a
row, the outcome is always 1. Then again, I don't anything about the
business, so maybe this is OK.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]