Divide By 0 Error In Oracle
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 how to avoid divide by zero error in oracle Learn more about Stack Overflow the company Business Learn more about hiring developers
Oracle Sql Divide By Zero Error
or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack oracle divide by zero exception 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: Sign up How to avoid DIVIDE BY ZERO error oracle divide by zero function 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 a particular month, recd_qty is ZERO because of which I am getting DIVIDE BY
Oracle Divide By Null
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 Feb 17 '11 at 10:42 vc 74 20.8k43763 The WHERE clause would not solve the problem as he is summing recd_qtys before dividing. –Tim Feb
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 enter a title. You can not post a blank message. Please type your message oracle divide two columns and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL oracle divide no remainder This discussion is archived 5 Replies Latest reply on Oct 11, 2010 10:31 AM by Robert Angel Handle divide by zero error
Divide By 0 Error Java
in SQL Robert Angel Oct 11, 2010 10:07 AM 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 http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query 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 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 https://community.oracle.com/thread/1558158 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. 13509Views 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/nullif(denominator,0) as div from t; Like Show 0 Likes(0) Actions 2. Re: Handle divide by zero error in SQL BluShadow Oct 11, 2010 10:15 AM (in response to odie_63) odie_63 wrote: 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.Yes, it did mention nullifying the divisor. Other t
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 http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm Don Burleson Blog
ORA-01476 divisor is equal to zero tips Oracle Error Tips by Burleson Consulting https://easyoradba.com/2013/08/23/ora-01476-divisor-is-equal-to-zero/ 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: divide by divisor is equal to 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 divide by zero 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, 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_DIVand sendemailEmail alert log ORA:errorsNew objects created in lastweekOracle Instance StatusRMANRMAN (Windows Script)RMAN Duplicating (for a Standby Database10g)RMAN Full(Weekly)RMAN Incremental(Daily)RMAN Simple (FullBackup)Script to Check RMAN Backup Status inReal-TimeUnix ScriptsCheck mount point status and sendemailGenerate HTML ReportSQL*PLUSSend Birthday EmailAlertsSpool Table output to CSV | ExcelfileWindows Scripts ORA-01476: divisor is equal tozero August 23, 2013 ~ easyoradba This error occurs when an expression is divided by zero. It is considered a logical error in Oracle. In mathematics, division by zero is division where the divisor (denominator) is zero. Such a division can be formally expressed as a/0 where a is the dividend (numerator). Whether this expression can be assigned a well-defined value depends upon the mathematical setting. In ordinary (real number) arithmetic, the expression has no meaning, as there is no number which, multiplied by 0, gives a (a≠0), and so division by zero is undefined. Since any number multiplied by zero is zero, the expression 0/0 has no defined value and is called an indeterminate form. In computing, a program error may lead to an attempt to divide a number by zero. Depending on the programming environment and the type of number (e.g. floating point, integer) being divided by zero, it may: generate positive or negative infinity by the IEEE 754 floating point standard, generate an exception, generate an error message, cause the program to terminate, or result in a special not-a-number value. Let us illustrate this with the help of an example.Lets assume a SELECT statement where you are doing a calculation based on the value inside a column. A simple example would be percentage calculation. Percentage is a fraction of