Hi
I have a SQL server 2005 environment. When i try to run a query (select) it gives me an error. Error message is: server:Â Msg8134, Level 16,State 1,Line 1 Divide by Zero error encountered.
Please help me.
Getting error when try to run a query
Hey heaven,
There are three possible solutions to this problem, i have also tried one of them that worked well for me.
Following are the three reasons:
1. CASE statement:
Use this query to solve this problem:
SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]
2. NULL IF/IS NULL:
Your query must look like this:
SELECT IS NULL([Numerator] / NULL IF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]
3. By setting ARITHABORT OFF and SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
SELECT [Numerator] / [Denominator]
All your queries must be look like the above quires to avoid the error.
Thanks
Getting error when try to run a query
The error is as a result of having a division operation whereas the denominator or dividing by 0. There is no error when the denominator or divisor is NULL as this will result to a NULL value.
There are several ways to avoid this error using SELECT STATEMENT:
-
NULLIF/ISNULL functions
-
SET ARITHABORT OFF and SET ANSI_WARNINGS OFF
-
CASE statement
The NULLIF and ISNULL functions, query will appear as follows
SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage]
FROM [Table1]
It changes the denominator into NULL incase it is zero. In division, any number divided by NULL results into NULL. Hence when the denominator is 0, the result of the division becomes NULL. Then to return a 0 value instead of a NULL value, the ISNULL function is used.
CASE will be as follows
Â
SELECT CASE WHEN [Denominator] = 0 THEN 0 ELSE [Numerator] / [Denominator] END AS [Percentage]
FROM [Table1]
Incase if the denominator or divisor is 0, the result is 0. Otherwise, then division operation is performed.
Hope you are enriched.
Let me know your progress. All the Best
Getting error when try to run a query
Fantastic! Error no occurrence. Thanks Boniface. Good advise in troubleshooting. I was already frustrated on thinking on solutions. I never thought this site could be the one could help me.
Thank you guys.
