Oracle Divisor Zero Error
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp oracle nullif divide by zero Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB
Java.sql.sqlexception Ora-01476 Divisor Is Equal To Zero
Don Burleson Blog
divide by zero exception in oracle 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 divideOra-01476 Divisor Is Equal To Zero Dbms_stats
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 shows this on the divide ora 01476 divisor is equal to zero catupgrd sql 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/SQL error exception to "trap" the ORA-01476 error with zero_divi
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies
Ora-01476 Exception Handling
of this site About Us Learn more about Stack Overflow the company
Divide By Zero Error Encountered In Sql Server
Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users vertica nullif Badges Ask 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 http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm 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) / 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 http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query 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 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
MySQL MariaDB PostgreSQL SQLite MS Office Excel Access Word Web Development https://www.techonthenet.com/oracle/errors/ora01476.php HTML CSS Color Picker Languages C Language More ASCII Table Linux UNIX Java Clipart Techie Humor Advertisement Oracle Basics ALIASES AND AND http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-l/ora1476-divisor-is-equal-to-zero-4848405 & OR BETWEEN COMPARISON OPERATORS DELETE DISTINCT EXISTS FROM GROUP BY HAVING IN INSERT INSERT ALL INTERSECT IS NOT NULL IS divide by NULL JOIN LIKE MINUS NOT OR ORDER BY PIVOT REGEXP_LIKE SELECT SUBQUERY TRUNCATE UNION UNION ALL UPDATE WHERE Oracle Advanced Oracle Cursors Oracle Exception Handling Oracle Foreign Keys Oracle Loops/Conditionals Oracle Transactions Oracle Triggers String/Char Functions Numeric/Math Functions Date/Time Functions Conversion Functions Analytic Functions divide by zero Advanced Functions Oracle / PLSQL: ORA-01476 Error Message Learn the cause and how to resolve the ORA-01476 error message in Oracle. Description When you encounter an ORA-01476 error, the following error message will appear: ORA-01476: divisor is equal to zero Cause You tried to divide a number by zero. Resolution The option(s) to resolve this Oracle error are: Option #1 Correct your code so that you do not divide a number by zero. Share this page: Advertisement Back to top Home | About Us | Contact Us | Testimonials | Donate While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy. We use advertisements to support this website and fund the development of new content. Copyright © 2003-2016 TechOnTheNet.com. All rights reserved.
Technology and Trends Enterprise Architecture and EAI ERP Hardware IT Management and Strategy Java Knowledge Management Linux Networking Oracle PeopleSoft Project and Portfolio Management SAP SCM Security Siebel Storage UNIX Visual Basic Web Design and Development Windows < Back CHOOSE A DISCUSSION GROUP Research Directory TOPICS Database Hardware Networking SAP Security Web Design MEMBERS Paul_Pedant DACREE MarkDeVries VoIP_News Inside-ERP MacProTX Inside-CRM I_am_the_dragon maxwellarnold Michael Meyers-Jouan TerryCurran Chris_Day Andrew.S.Baker Ramnath.Awate JoeTorre Craig Borysowich Locutus Dennis Stevenson DukeGanote Richard iudithm mircea_luca Clinton Jones bracke Nikki Klein AbhaiTripathi Iqbalyk Adrian_Grigoriu bluesguyAZ59 numbersguyPA COMPANIES EdgeWave Sophos Pivotal CRM Wave Direct View All Topics View All Members View All Companies Toolbox for IT Topics Oracle Groups Ask a New Question Oracle Database This group is where peers share technical expertise, solve problems, and discuss issues related to the use of Oracle Databases, including Oracle Grid. Home | Invite Peers | More Oracle Groups Your account is ready. You're now being signed in. Solve problems - It's Free Create your account in seconds E-mail address is taken If this is your account,sign in here Email address Username Between 5 and 30 characters. No spaces please The Profile Name is already in use Password Notify me of new activity in this group: Real Time Daily Never Keep me informed of the latest: White Papers Newsletter Jobs By clicking "Join Now", you agree to Toolbox for Technology terms of use, and have read and understand our privacy policy. ORA-1476: Divisor is Equal to Zero kool999 asked Jul 11, 2012 | Replies (11) I need help on ora-1476: divisor is equal to zero Hi, I have this formula in my script: (SUM( b.cost_price_n)/100)/ ((count (b.rate_id_n) + SUM(b.end_units_n - b.start_units_n ))/10240) as calculated_tariff and it gives me error ora-1476: divisor is equal to zero when I run the script. Can anyone help? Join this group Best Answer Updated html error So just looking at this, it looks like any time count(b.rate_id_n) + SUM(b.end_units_n = b.start_units_n) = 0 you will have a problem. You have a couple of choices: 1) Use the following SQL statement to determine which