Oracle Division By Zero Error
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 oracle nullif Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation
Divide By Zero Error Encountered. In Sql
Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just vertica nullif like you, helping each other. Join them; it only takes a minute: Sign up How to avoid DIVIDE BY ZERO error in an SQL query up vote 8 down vote favorite 7 SELECT YEAR, period, round((1- sum(rej_qty) /
Divide By Zero Error Encountered Excel
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 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 error encountered in stored procedure divide-by-zero share|improve this question edited Oct 1 '12 at 8:16 Aziz Shaikh 11.6k73954 asked Feb 17 '11 at 10:38 HanuAthena 5,293226895 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 160k14204250 answered Feb 17 '11 at 10:42 vc 74 20.9k43763 The WHERE clause would not solve the problem as he is summing recd_qtys before dividing. –Tim Feb 17 '11 at 10:43 @Tim Thanks, I've changed the SQL for a subquery –vc 74 Feb 17 '11 at 10:50 @Kenny That's the thing... it may not be accurate :) –vc 74 Feb 18 '11 at 8:48 It would be perfectly legal for Ora
2007 - 1:28 pm UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: Those pesky dates as strings Latest Followup You Asked Hi Tom, My problem is handling of
Oracle Divide By Zero Function
zero_divide exception. I'm using INSERT INTO T1 (SELECT... FROM T2) syntax. Tables T1 and
Sql Divide By
T2 are known only at the runtime. SELECT contains many calculations as per example: SELECT C1/C2,C3+(C4/C5)... FROM T2 I would like to redshift nullif identify rowid of the row raising zero_devide exception. Thank you very much for everything that you done on this site. It is very helpful for me. Best regards. Mladen and we said... select decode( c2, http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query 0, to_number(null), c1/c2 ) will accomplish your goal safely. Use decode to avoid evaluating a zero divide condition. Note: the to_number 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:16179197301219 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 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:
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 http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm 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 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 by zero 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 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 by zero error 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, 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