How To Solve Divide By Zero Error In Sql
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 divide by zero error encountered in sql server Stack Overflow the company Business Learn more about hiring developers or posting ads with
Oracle Sql Divide By Zero
us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a divide by zero error encountered excel community of 4.7 million programmers, just like you, helping 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
Msg 8134 Level 16 State 1 Line 1 Divide By Zero Error Encountered
favorite 46 I have this error message: Msg 8134, Level 16, State 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 nullif sql treatment for zero. Is the best way to use a NullIf clause? Is there better way, or how can this be enforced? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 J.D. 4311525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,92331220 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 352 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,92331220 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 y
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
Divide By Zero Error Encountered In Stored Procedure
8134) Divide by Zero Error Encountered in SQL Server?Before we see
Divide By Zero Error Encountered. The Statement Has Been Terminated
the answer of this question, let us see how to recreate this error.Run following script in divide by 0 in 128 bit arithmetic netezza SQL Server Management Studio window.DECLARE @Var1 FLOAT; DECLARE @Var2 FLOAT; SET @Var1 = 1; SET @Var2 = 0; SELECT @Var1/@Var2 MyValue;When you execute above script you will see http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql 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 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 http://blog.sqlauthority.com/2016/08/27/sql-server-fix-error-8134-divide-zero-error-encountered/ 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 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 Server340Related Articles SQL SERVER - Group by Rows and Columns using XML PATH - Efficient Concating Trick April 5, 2013Pinal Dave SQL SERVER - Maximum Columns per Primary Key
OK, some of the data I am using isnt playing nicely and once in a while I get Divide by zero error encountered. Divide by zero error encountered. Msg 8134, Level 16, https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html State 1, Line 2 To avoid this I am using a CASE as: DECLARE @Int1 AS INT , @Int2 AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1 / CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the same results but divide by without littering by nice tidy SELECT statements with hundreds of CASE WHEN ... THEN ... ELSE ... END's?? Am I missing some super useful function that does this in one line? [edit]OK, thanks for the answers so far. I dont want to lose rows where it is zero so, I cant accept a NULL in its place or to filter out those rows with a WHERE divide by zero @int20 I simply want to replace a 0 value with a 1 value... more ▼ 0 total comments 973 characters / 178 words asked Oct 14, 2010 at 08:54 AM in Default Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 edited Oct 14, 2010 at 11:03 AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first ▼ oldest newest voted first 0 You can use NULLIF if you want to return null when division by zero: SELECT @int1 / NULLIF(@int2,0) -- returns NULL or SELECT @int1 / ISNULL(NULLIF(@int2,0),1) -- returns @int1 The CASE statement is probably much faster then the second option, but you can test the performance more ▼ 11 total comments 288 characters / 46 words answered Oct 14, 2010 at 09:04 AM Håkan Winther 16.5k ● 36 ● 45 ● 57 edited Oct 14, 2010 at 09:07 AM Always though about CASE on this one. Good to know about NULLIF Oct 14, 2010 at 09:07 AM ozamora Håkan Winther Nope, the case statement is not any faster because NULLIF is the case st