Oracle Sql Divide By Zero Error Encountered
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 oracle nullif this site About Us Learn more about Stack Overflow the company Business divide by zero error encountered excel Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask msg 8134 level 16 state 1 line 1 divide by zero error encountered Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up sql nullif How to avoid the “divide by zero” error in SQL? up vote 188 down vote favorite 47 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
Sql Nullif 0
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 J.D. 4311525 asked May 14 '09 at 6:06 Henrik Staun Poulsen 4,93331220 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 353 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,93331220 that's the way I would have s
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 part
Divide By Zero Error Encountered In Stored Procedure
in a four part series that discusses the SQL NULL value. I haven't read redshift nullif the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent vertica nullif divide-by-zero 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:
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books http://dbaforums.org/oracle/index.php?showtopic=1818 Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-01476 divisor is equal to zero tips Oracle Error Tips by Burleson Consulting Question: I?m divide by using this query and it's returning a divide by zero error: SELECT STUSECMTAMT.totdebit/STUSECMTAMT.totalcr, STUJOURNAL.cre_bal/(STUSECMTAMT.totdebit/STUSECMTAMT.totalcr) FROM STUSECMTAMT,STUDEG,STUJOURNAL WHERE STUSECMTAMT.vhno=STUJOURNAL.ref_no AND STUSECMTAMT.studegid=STUDEG.studegid;ERROR at line 1:ORA-01476: divisor is equal to zero Can anyone shed a bit more light on how to detect and handle a divide by zero divide by zero error?Answer: The Oracle oerr utility shows this on the divide by zero ORA-01476 error: ORA-01476 divisor is equal to zero Cause: An expression attempted to divide by zero. Action: Correct the expression, then retry the operation. You are dividing by 0. In your case, either: STUSECMTAMT.totalcr, (STUSECMTAMT.totdebit/STUSECMTAMT.totalcr) or STUSECMTAMT.totalcr equates to 0. You could use a decode or a case to capture the 0 and dtrap the condition without aborting the SQL. Resolving divide by zero using DECODE In the following example, a zero will be returned whenever the divisor is 0.DECODE(STUSECMTAMT.totalcr,0,0,STUSECMTAMT.totdebit / STUSECMTAMT.totalcr) Resolving divide by zero using CASE You could also try a CASE statement.CASE WHEN STUSECMTAMT.totalcr = 0 THEN 0 ELSE STUSECMTAMT.totdebit / STUSECMTAMT.totalcr END Using zero_divide to handle a divide by zero error In addition to using DECODE and CASE, anothe