Getting error when try to run a query

Asked By 20 points N/A Posted on -
qa-featured

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.

SHARE
Best Answer by bonifacekimeu
Answered By 10 points N/A #101612

Getting error when try to run a query

qa-featured

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

Best Answer
Best Answer
Answered By 0 points N/A #101613

Getting error when try to run a query

qa-featured

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

Answered By 20 points N/A #101614

Getting error when try to run a query

qa-featured

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.

Login/Register to Answer

Related Questions