Getting error when try to run a query

Asked By heaven regis 20 points N/A Posted on - 03/08/2012

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.

• Status: Open
• Question Views: 920
• Answer Count: 3
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by bonifacekimeu

Go To Solution

Answered By Tunacao_Caaron1 20 points N/A #101612

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

About Tunacao_Caaron1

Questions
1

Answers
399

Best Answers
138

• Vote Up 0 Vote Down

• Posted on - 03/12/2012
• Question Category: MS SQL Server

Best Answer

Best Answer

Answered By bonifacekimeu 0 points N/A #101613

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

About bonifacekimeu

Questions
0

Answers
31

Best Answers
9

• Vote Up 0 Vote Down

• Posted on - 03/13/2012
• Question Category: MS SQL Server

Answered By heaven regis 20 points N/A #101614

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.

About heaven regis

Questions
25

Answers
4

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 04/19/2012
• Question Category: MS SQL Server