Divide By Zero Error Encountered. In Sql 2005
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more divide by zero error encountered in sql server about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Divide By Zero Error Encountered In Sql Server 2012
Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping
Divide By Zero Error Encountered Sql Server 2008
each other. Join them; it only takes a minute: Sign up How to avoid the “divide by zero” error in SQL? up vote 188 down vote favorite 46 I have this error message: Msg 8134, Level 16, State
Sql Divide By Zero Error Encountered Nullif
1, Line 1 Divide by zero error encountered. What is the best way to write SQL code so that I will never see this error message again? I could do either of the following: Add a where clause so that my divisor is never zero Or I could add a case statement, so that there is a special treatment for zero. Is the best way to use a NullIf clause? Is there better way, or how can tsql divide by zero error encountered this be enforced? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 Hooper 4241525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 4 Perhaps some data validation is in order. –Anthony May 14 '09 at 19:17 add a comment| 15 Answers 15 active oldest votes up vote 350 down vote accepted In order to avoid a "Division by zero" error we have programmed it like this: Select Case when divisor=0 then null Else dividend / divisor End ,,, But here is a much nicer way of doing it: Select dividend / nullif(divisor, 0) ... Now the only problem is to remember the NullIf bit, if I use the "/" key. share|improve this answer edited Dec 20 '12 at 1:04 Community♦ 11 answered May 14 '09 at 6:10 Henrik Staun Poulsen 4,89331220 that's the way I would have solved it. –J. Polfer May 14 '09 at 19:21 4 A much nicer Way of doing it "Select dividend / nullif(divisor, 0) ..." breaks if divisor is NULL. –Anderson Dec 1 '14 at 10:51 add a comment| up vote 87 down vote In case you want to return zero, in case a zero devision would happen, you can use: SELECT COALESCE(dividend / NULLIF(divisor,0), 0) FROM sometable For every divisor that is zero, you will get a zero in the result set. share|imp
SERVER - How to Fix Error 8134 Divide by Zero Error Encountered August 27, 2016Pinal DaveSQL Tips and Tricks5 commentsHere is one of the most popular questions: How to overcome (Error 8134) Divide by Zero Error Encountered in SQL Server?Before we see the answer of this oracle sql divide by zero question, let us see how to recreate this error.Run following script in SQL Server msg 8134 level 16 state 1 line 1 divide by zero error encountered Management Studio window.DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/@Var2 MyValue;When you execute divide by zero error encountered excel above script you will see that it will throw error 8134. Msg 8134, Level 16, State 1, Line 6 Divide by zero error encountered. Here is the screenshot of the error.Now there are multiple ways http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql to avoid this error to happen. We will see two of the most popular methods to overcome this error.Method 1: Use NullIf FunctionHere is the SQL script with NullIf FunctionDECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/NULLIF(@Var2,0) MyValue;When you use the NULLIF function, it converts the zero value to Null and leading to the entire result set to be NULL instead of an http://blog.sqlauthority.com/2016/08/27/sql-server-fix-error-8134-divide-zero-error-encountered/ error.Method 2: Use Case StatementHere is the SQL script with CASE StatementDECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT CASE WHEN @Var2 = 0 THEN NULL ELSE @Var1/@Var2 END MyValue;When you use CASE statement, it converts the zero value to Null and leading to the entire result set to be NULL instead of an error.Let me know if you have any other alternate solution. I will be happy to publish in the blog with due credit.Reference: Pinal Dave (http://blog.sqlauthority.com) Tags: SQL Error Messages, SQL Server330Related Articles SQL SERVER - Performance Comparison - INSERT TOP (N) INTO Table - Using Top with INSERT March 3, 2010Pinal Dave SQL SERVER - Unable to Bring SQL Cluster Resource Online - Online Pending and then Failed August 14, 2015Pinal Dave SQL SERVER - 2005 - SSMS - View/Send Query Results to Text/Grid/Files July 19, 2007Pinal Dave 5 comments. Leave new subbu444 August 27, 2016 10:27 amHi,Please check the below code to avoid 8134 error.DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = "; -0, 1, NULL," IF(@Var2=0) SELECT NULL; ELSE SELECT @Var1/@Var2;Regards, SubbaReddy AVReply Pinal Dave August 27, 2016 4:19 pmThat should work as well. Thanks for sharing.Reply Azhar August 28, 2016 12:
3001-4000 Messages 4001-5000 Messages 5001-6000 Messages 6001-7000 Messages 7001-7500 Messages 7501-8000 Messages 8001-8500 Messages 8501-9000 Messages 9001-9500 Messages 9501-10000 Messages 10001-10500 Messages 10501-11000 Messages 11001-11500 Messages 11501-12000 Messages 12001-13000 Messages 13001-13500 Messages 14001-14500 http://www.sql-server-helper.com/error-messages/msg-8134.aspx Home>SQL Server Error Messages> Msg 8134 - Divide by zero error encountered SQL Server Error Messages - Msg 8134 - Divide by zero error encountered SQL Server Error Messages - Msg 8134 Error Message Server: http://www.itjungle.com/fhg/fhg051210-story02.html Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. Causes: This error is caused by performing a division operation wherein the denominator or the divisor is 0. This error is not divide by encountered when the denominator or divisor is NULL because this will result to a NULL value. Solution / Work Around: There are three ways to avoid the "Division by zero encountered" error in your SELECT statement and these are as follows: CASE statement NULLIF/ISNULL functions SET ARITHABORT OFF and SET ANSI_WARNINGS OFF Using the CASE statement, your query will look like the following: SELECT CASE WHEN [Denominator] = 0 THEN divide by zero 0 ELSE [Numerator] / [Denominator] END AS [Percentage] FROM [Table1] If the denominator or divisor is 0, the result becomes 0. Otherwise, the division operation is performed. Using the NULLIF and ISNULL functions, your query will look like the following: SELECT ISNULL([Numerator] / NULLIF([Denominator], 0), 0) AS [Percentage] FROM [Table1] What this does is change the denominator into NULL if it is zero. Then in the division, any number divided by NULL results into NULL. So if the denominator is 0, then the result of the division will be NULL. Then to return a value of 0 instead of a NULL value, the ISNULL function is used. Lastly, using the SET ARITHABORT and SET ANSI_WARNINGS, your query will look like the following: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT [Numerator] / [Denominator] With both ARITHABORT and ANSI_WARNINGS set to OFF, SQL Server will return a NULL value in a calculation involving a divide-by-zero error. To return a 0 value instead of a NULL value, you can put the division operation inside an ISNULL function: SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT ISNULL([Numerator] / [Denominator], 0) Related Articles : Frequently Asked Questions - SQL Server Error Messages Frequently Asked Questions - INSERT Statement Frequently Asked Questions - SELECT Statement
8 knows that division by zero is a no-no, or at least they should. Everybody who's used SQL for any amount of time knows that we can use a CASE expression to prevent division by zero. But do you know the other way to prevent division by zero in SQL? Assume a database file called SomeTable, with fields called Quantity and Amount. Let's divide Amount by Quantity. First, here's the usual case statement. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 0 end from SomeTable If Quantity is not zero, the division takes place. If Quantity is zero, the calculation yields a zero. We could just as easily return any other numeric value, the value one, for example. select itnbr, Amount, Quantity, case when Quantity <> 0 then Amount / Quantity else 1 end from SomeTable The other way to prevent division by zero is to use the NULLIF function. NULLIF requires two arguments. If the arguments are equal, NULLIF returns a null value. If they are not equal, NULLIF returns the first value. Here's the SQL. select itnbr, Amount, Quantity, Amount / nullif(Quantity,0) from SomeTable If Quantity is not zero, the two arguments do not match, and the resulting calculation is Amount divided by Quantity. But if Quantity is zero, the arguments do match, and the calculation is Amount divided by null, which yields a null value. If you use this technique, be sure to put the zero in the second argument of NULLIF. Post this story to del.icio.us Post this story to Digg Post this story to Slashdot Sponsored By SEQUEL SOFTWARE SEQUEL ViewPoint--Data Access & Analysisfor Power Systems Servers Easy to use by IT and end users Real-time data access and analysis Queries, reports, pivot tables, drill-down analysis, kpi dashboards IBM i-centric with multiplatform data access Expert support a