Oracle Divide By Zero Error Encountered
Contents |
Matt Woodward (@mpwoodward) , and Kurt Wiersma Using 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 an article that is the final oracle sql divide by zero part in a four part series that discusses the SQL NULL value. I haven't read divide by zero error encountered in sql server the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero
Divide By Zero Error Encountered Excel
errors in a SQL call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a SQL call. Therefore, running this code:
2007 - 1:28 pm UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: FOLLOWS clause Latest Followup You Asked Hi Tom, My problem is handling of zero_divide exception. I'm using INSERT INTO T1 (SELECT... FROM T2) syntax. Tables
Divide By Zero Error Encountered In Stored Procedure
T1 and T2 are known only at the runtime. SELECT contains many calculations as per example: error divide by 0 in 128 bit arithmetic in netezza SELECT C1/C2,C3+(C4/C5)... FROM T2 I would like to identify rowid of the row raising zero_devide exception. Thank you very much for everything that you sql nullif done on this site. It is very helpful for me. Best regards. Mladen and we said... select decode( c2, 0, to_number(null), c1/c2 ) will accomplish your goal safely. Use decode to avoid evaluating a zero divide condition. Note: the to_number https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm is not "extra", it is necessary to avoid implicit conversions to and from strings. Note2: I'm assumging when c2 is zero, the returned value should be "unknown", if it should be zero or some other number, it is OK to return that as well: decode( c2, 0, 0, c1/c2 ) Reviews Write a Review More or less the same problem February 17, 2005 - 9:29 am UTC Reviewer: Gabriel from Portugal My problem is that we have an application where the https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:16179197301219 end user can introduce a formula, wich will eventually turned into a dynamic sql. The idea is to return a null if the expression encounters a zero-divide expression. Our options are - Use a function to wrap around the expression that would catch the zero-divide exception. I don't know how to solve this. - Parse the expressions the user can introduce. This is difficult to do. Do you know a way to implement the first solution ? Something I could use as follows : SELECT myfunc(C1/C2),myfunc(C3+(C4/C5))... FROM T2 Thank you so much for this and for your books, they have been very helpful. Cheers Gabriel Followup February 17, 2005 - 10:37 am UTC parsing the expression would not help unless the function returns a constant value, in which case -- it would not be much of a function! I'm not liking the idea, before I answer how to do in a function, tell me -- how many of these functions are there? why are we not using a view? how often are they executed? Sounds Dangerous ? February 17, 2005 - 11:10 am UTC Reviewer: Paul from London, UK "My problem is that we have an application where the end user can introduce a formula, wich will eventually turned into a dynamic sql" If I understand that correctly you are taking the user input (a formula) and including it directly in your SQL query. Sounds like a huge
Messages 2001-3000 Messages 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 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: 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 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 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 erro