Divide by zero error! Help!

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'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>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'
>

Divide by zero error! Help!

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'Hi
Don't assume that the execution order of an ELSE will follow the order you
coded. It may evaluate both CASEs in parallel and then use the output later.
During query execution, your query may be run differently to what you think.
This can be influenced by number of processors, RAM available, indexes and
statistics.
You need to make sure that your query does not have the possibility of
failing, no matter the code execution path.
Regards
--
Mike
This posting is provided "AS IS" with no warranties, and confers no rights.
<zod91@.yahoo.com> wrote in message
news:1149560228.320495.202850@.j55g2000cwa.googlegroups.com...
>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'
>

Divide by zero error! Help!

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'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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

divide by 0

dear all,
hi, i got this problem - Divide by zero error encountered.

can someone please help me and this is the code

--cast(
Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)
--as numeric(3,2))

thank you very much indeed.

regards,
Catcycyou should drop a Case clause in which will execute in place of these when the value you're using is zero, OR you should filter the data so you don't get zeroes in the first place.|||Or if you just want to suppress the error, use

SET ARITHABORT ON | OFF|||dear Dutch,
i've use the codes that you recommended but still cannot generate the thing that i want.

this is my first time to generate report that calculate percentage by using sql statement & also the hard part is to check how many pending case within certain period as below code :

Sum(Case
When Proj_Status = 'Pending' and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
Else 0
End) * 100 /
Sum(Case
when m02.ID = m01.BoardID and m01.created >='2003-06-12' AND
m01.created <'2003-06-14' then 1
else 0
end)

i was thinking can i use ifelse statement to check before the above code to prevent this error. But i'm not really familiar with the usage of if else statement in sql.

so, can you teach me how to use or anyone can show me.

regards,
Catcyc