Divide By Zero Error In Oracle
Contents |
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please oracle nullif enter a title. You can not post a blank message. Please type
Oracle Divide By Zero Exception
your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This oracle divide by zero function discussion is archived 5 Replies Latest reply on Oct 11, 2010 10:31 AM by Robert Angel Handle divide by zero error in SQL Robert Angel Oct 11, 2010 10:07 AM
Vertica Nullif
Hi, a few posts back someone asked the best way to handle divide by zero. Can I revisit this theme with, what is the best way to handle divide by error in SQL without repeating code... What I have in mind is quite frequently divided by error appears as the result of a lengthy formula calculation - and trying to oracle divisor is equal to zero burying a lengthy a lengthy formula calculation in a case statement gives you an exceedingly long result, which is not great for code maintenance. Wrapping the handling in a function is also not great for reasons of performance. So is there any simple way to do the equivalent of (pseudo code) select IFerr( actual / (RidiculouslyLongConvulutedFormulaHereWhichSometimesYieldsZeroValues), 0) from dual Where IFerr works like NVL - except it yields 0 when the result is a number error, or the result otherwise? thanks, Robert. 13507Views Tags: none (add) This content has been marked as final. Show 5 replies 1. Re: Handle divide by zero error in SQL odie_63 Oct 11, 2010 10:15 AM (in response to Robert Angel) Hi, a few posts back someone asked the best way to handle divide by zero.I didn't see that thread, but does it mention NULLIF function? You can apply it to every denominator in your formula, so that it yields NULL in case it evaluates to 0. For example, with t as ( select 1 numerator, 0 denominator from dual ) select numerator/nulli
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
Divide By Zero Error Encountered In Stored Procedure
of this site About Us Learn more about Stack Overflow the company
How To Avoid Divide By Zero Error In Sql
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges sql nullif Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: https://community.oracle.com/thread/1558158 Sign up How to avoid DIVIDE BY ZERO error in an SQL query up vote 8 down vote favorite 6 SELECT YEAR, period, round((1- sum(rej_qty) / sum(recd_qty))*100, 0) FROM TAB_A WHERE sid = '200' AND sdid IN ('4750') AND ( ( YEAR ='2011' AND period IN('01_JAN') ) OR ( YEAR = '2010' AND period IN('02_FEB','03_MAR','04_APR','05_MAY','06_JUN','07_JUL','08_AUG','09_SEP','10_OCT','11_NOV','12_DEC') ) ) group by year, period For http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query a particular month, recd_qty is ZERO because of which I am getting DIVIDE BY ZERO error. Is there any way to avoid DIVIDE BY ZERO error? I there any way where in that particular month is ignored? sql oracle9i divide-by-zero share|improve this question edited Oct 1 '12 at 8:16 Aziz Shaikh 11.5k73753 asked Feb 17 '11 at 10:38 HanuAthena 5,258226695 add a comment| 3 Answers 3 active oldest votes up vote 9 down vote accepted If you want to ignore such records you can use a subquery SELECT YEAR, period, round((1- rej_sum / recd_sum)*100, 0) FROM ( SELECT YEAR, sum(rej_qty) rej_sum, sum(recd_qty) recd_sum FROM TAB_A WHERE sid = '200' AND sdid IN ('4750') AND ( ( YEAR ='2011' AND period IN('01_JAN') ) OR ( YEAR = '2010' AND period IN ('02_FEB','03_MAR','04_APR','05_MAY','06_JUN','07_JUL','08_AUG','09_SEP','10_OCT','11_NOV','12_DEC') ) ) group by year, period ) WHERE recd_sum <> 0; If you want to keep them and handle the division by zero issue, you can use decode or case SELECT YEAR, period, DECODE(recd_qty, 0, NULL, round((1- sum(rej_qty) / sum(recd_qty))*100, 0)) share|improve this answer edited Feb 18 '11 at 21:32 Justin Cave 159k14203250 answered
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm Burleson Blog
http://psoug.org/definition/NULLIF.htm ORA-01476 divisor is equal to zero tips Oracle Error Tips by Burleson Consulting Question: I?m 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 divide by zero Can anyone shed a bit more light on how to detect and handle a 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, divide by zero 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, another option is to trap the error in PL/SQL with the zero_divide option. Just make a PL/SQL error exception to "trap" the ORA-01476 error with zero_divide EXCEPTION WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.put_line('Zero divide error - Try again);END; Or, you could replace the zero with small value (.000001) and computer the equation: EXCEPTION WHEN ZERO_DIVIDE THEN :divisor_var := .000001; END; Lastly, you could replace the output of a divide by zero equation with a zero return value: EXCEPTION WHEN ZERO_DIVIDE THEN return 0; END; �ᦙCode Library PHP Code Library JavaScript Code Library Oracle Terms & Definitions Oracle Error Codes PSOUG Community Blogs Oracle Jobs Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and Events Sponsors Page Submit Code Contact Us Looking for the original pages? (formerly called "Morgan's Library") You can find them here. Term: NULLIF Definition: The Oracle NULLIF function is a null handling function which accepts two numeric arguments. If both of the arguments are equal it returns NULL; otherwise it returns the first argument. It is most commonly used for handling the ZERO_DIVIDE exception. Example Syntax: NULLIF(arg1, arg2) where arg1 and arg2 are of the NUMBER datatype. Example Usage: The SQL example below raises a ZERO_DIVIDE exception as it foolishly tries to divide a number by zero. SQL> SELECT 12/0 FROM DUAL; select 12/0 from dual * ERROR at line 1: ORA-01476: divisor is equal to zero The SQL example below handles the exception raised above by using the NULLIF function in the Divisor. SQL> SELECT 12/NULLIF(0,0) FROM DUAL; 12/NULLIF(0,0) -------------- Related Links: Miscellaneous Functions: NULLIF SQLLoader: Example using NULLIF Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 54 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?