Avoid Divide By Zero Error 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 Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack divide by zero error sql server Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack
Sql Divide By Zero Error Nullif
Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up How to avoid sql divide by zero error encountered the “divide by zero” error in SQL? up vote 188 down vote 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
Tsql Divide By Zero Error Encountered
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 this be enforced? sql sql-server sql-server-2005 sql-server-2008 share|improve this question edited Jan 6 at 19:50 Hooper 4191525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,89331220 oracle sql divide by zero 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♦ 1 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|improve this answer edited Jan 15 '13 at 19:41 Peter Mortensen 10.2k1369107 answered Jan 4 '12 at 12:06 Tobias Domhan 1,4431011 8 Some benchmarks reveal that COALESCE is slightly slower than ISNULL. However, COALESCE is in the standards so is more portable. –Paul Chernoch Jul 12 '12 at 14:29 16 If someone else d
NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to
Divide By Zero Error Encountered Excel
an article that is the final part in a four part series
Msg 8134 Level 16 State 1 Line 1 Divide By Zero Error Encountered
that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a sql nullif really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero errors in a SQL call.The idea here is that, as with any other form of math that http://stackoverflow.com/questions/861778/how-to-avoid-the-divide-by-zero-error-in-sql I know of, you cannot divide by zero in a SQL call. Therefore, running this code:
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 http://www.itjungle.com/fhg/fhg051210-story02.html 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, http://blog.sqlauthority.com/2014/12/29/sql-server-puzzle-why-divide-by-zero-error/ 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 divide by 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 divide by zero 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 and training Secure data access GUI, Web & green screen interface SEQUEL--Fast, efficient & cost-effective data analysis Click here for a FREE Information Kit! Senior Technical Editor: Ted Holt Technical Editor: Joe Hertvik Contributing Technical Editors: Erwin Earley, Brian Kelly, Michael Sansoterra Publisher and Advertising Director: Jenny Thomas Advertising Sales Representative: Kim Reed Contact the Editors: To contact anyone on the IT Jungle Team Go to our contacts page and send us a message. Sponsored Links Help/Systems: Robot/SCHEDULE Enterprise for UNIX, Linux, Windows & i looksoftware: RPG Open Access Webinar - May 18 at 10am (GMT) & May 19 at 2pm (EDT) Essex Technology Group: May 18-20: IB
SERVER - Puzzle - Why Divide by Zero Error December 29, 2014Pinal DaveSQL, SQL Puzzle, SQL Server, SQL Tips and Tricks35 commentsEarlier I asked a puzzle SQL SERVER - Puzzle - Why Decimal is Rounded Up? and it was very well received by all of you. You can read various comments posted in the blog post and they are indeed very enlightening. After posting that blog post, I received quite a lots of emails asking for more puzzle similar to that. Based on your request, here is another puzzle which is very similar to the earlier puzzle but have a very different approach.The question is why following T-SQL gives a famous divide by zero error.SELECT 10000/(17/17/17) AS Result2;When we execute above query it gives following error:Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.Here is the screenshot of the error.Hint: Read the comments in this blog post.I will be announcing the winner of this puzzle in my newsletter. There will be a surprise gift of USD 29. Leave your answers in the comment.Reference: Pinal Dave (http://blog.sqlauthority.com) 445 35 comments. Leave new 亂馬客 December 29, 2014 7:06 amInt / int = int 17/17 => 1 1/17 => 0 10000/0 => Divide by zero error encountered. Thanks.Reply Logan December 29, 2014 8:04 amThe denominator resolves to 0 since all numbers are integers and inside of the parenthesis the division is solved from left to right. 17/17 = 1 and then 1/17 = .0588 which is rounded to 0 to maintain the integer data type. Now the numerator is divided by 0 and throws an errorReply Anu December 29, 2014 8:59 amIn this situation SQL will translate 17 as an int so it is rounding the result to 0 and then attempting to divide 10000 by 0. Since division is a distribution and we can't distribute any number against 0. If you would like see SQL perform this math you can write "select 10000/(17.00/17.00/17.00) AS Result2" then we will get result in decimal or float however even this won't be completely accurate because SQL will do some rounding during the calculation.Reply lethalwarriors December 29, 2014 9:33 amHi Pinal, Drivin