Divide By Zero Error Encountered 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 Learn more about Stack
Divide By Zero Error Encountered Excel
Overflow the company Business Learn more about hiring developers or posting ads with us divide by zero error encountered in stored procedure Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community
Divide By Zero Error Encountered In Crystal Report
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 in an SQL query up vote 8 down vote divide by zero error encountered in sql server 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 ZERO error. Is there any way to avoid DIVIDE BY ZERO error? I there divide by zero error encountered in sql server 2012 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 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..
2007 - 1:28 pm UTC Category: Database – Version: 8.1.7 Latest Followup You Asked Hi Tom, My problem is handling of zero_divide exception. I'm using INSERT
Divide By Zero Error Encountered Sql Server 2008
INTO T1 (SELECT... FROM T2) syntax. Tables T1 and T2 are known divide by zero error encountered ssrs only at the runtime. SELECT contains many calculations as per example: SELECT C1/C2,C3+(C4/C5)... FROM T2 I would like
Tsql Divide By Zero Error Encountered
to 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. http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query 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 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 https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:16179197301219 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 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
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books 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 divide by 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 error?Answer: The Oracle oerr utility divide by zero 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, another option is to trap the error in PL/SQL with the zero_divide option. Just make a PL/SQ