Oracle Handle Divide 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 oracle nullif divide by zero of this site About Us Learn more about Stack Overflow the company divide by zero error encountered. in sql Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users
Divide By Zero Error Encountered Excel
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
Vertica Nullif
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 oracle divide by zero exception 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
2007 - 1:28 pm UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: Why PLSQL ? Latest Followup You Asked Hi Tom,
Sql Divide By
My problem is handling of zero_divide exception. I'm using INSERT INTO T1 (SELECT... divide by zero error encountered in stored procedure FROM T2) syntax. Tables T1 and T2 are known only at the runtime. SELECT contains many calculations as per redshift nullif example: SELECT C1/C2,C3+(C4/C5)... FROM T2 I would like 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 http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query helpful for me. Best regards. 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 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:16179197301219 zero 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 which case -- it would not be much of a function! I'm not likin
Code Library PHP Code http://psoug.org/definition/NULLIF.htm Library JavaScript Code Library Oracle Terms & Definitions Oracle http://searchoracle.techtarget.com/answer/Using-ISNULL-or-NULLIF-to-prevent-division-by-zero 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 divide by 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 divide by zero 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 77 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?
Topic SQL Development View All BPEL in Oracle Open Source Java / J2EE Stored procedures XML PL/SQL Applications View All Enterprise and business performance management Implementing and upgrading Oracle apps E-Business Suite Fusion applications Hyperion JD Edwards (JDE) PeopleSoft Siebel and Oracle CRM Data Mgmt View All BI (business intelligence) Data quality Data warehousing Metadata Database Admin View All Cloud infrastructure Availability Backup and recovery Database design Export, import and migration Installation, upgrades and patches Oracle performance problems and tuning Oracle security Oracle DBA tools Error messages MySQL database Real Application Clusters (RAC) Development View All BPEL in Oracle Open Source SQL Java / J2EE Stored procedures XML PL/SQL Fusion View All Oracle and BEA Application Server Data and application integration SOA (service-oriented architecture) Infrastructure View All Cloud computing infrastructure Exadata and Exalogic Grid computing Oracle on Linux Operating system Oracle hardware decisions Virtual machine Oracle management View All certification Oracle acquisitions Business process management Market analysis DBA jobs training and certification Small businesses Regulatory compliance Outsourcing Oracle on demand and SaaS Oracle strategy and product roadmap Oracle support services Oracle vs. SAP Oracle Web 2.0 Sun-Oracle infrastructure View All Oracle cloud computing Oracle OS Oracle virtualization Topics Archive View All Oracle DBA jobs Oracle Resources Training and certification Tutorials, tips and FAQs Please select a category Applications Data Mgmt Database Admin Development Fusion Infrastructure Oracle management Sun-Oracle infrastructure Section Problem Solve News Get Started Evaluate Manage Problem Solve Sponsored Communities Q Using ISNULL or NULLIF to prevent division by zero Sections Share this item with your network: Related Expert Q&A Querying to get NULLs – SearchDataManagement Cumulative sum across the row, not down the column – SearchSQLServer Populating one name column from three – SearchOracle Sponsored News Optimize Your Operational Effec