Divide By Zero Oracle 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 how to avoid divide by zero error in oracle Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation oracle sql divide by zero error Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just oracle divide by zero exception 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 6 SELECT YEAR, period, round((1- sum(rej_qty) / oracle divide by zero function 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
Oracle Nullif Divide By Zero
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... it may not be accurate :) –vc 74 Feb 18 '11 at 8:48 It would be perfectly legal for Oracl
Library
Prevent Divide By Zero Sql
mySQL Code Library PHP Code Library JavaScript Code oracle sql divide by zero Library Oracle Terms & Definitions Oracle Error Codes PSOUG Community Blogs Oracle Jobs oracle nullif Board PSOUG Forum Oracle User Group Directory Free Oracle Magazines Online Learning Center PSOUG Presentations Advanced Code Search News and http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query Events Sponsors Page Submit Code Contact Us Looking for the original pages? (formerly called "Morgan's Library") You can find them here. Term: ZERO_DIVIDE Definition: The ZERO_DIVIDE Exception (ORA-01476) occurs when a program attempts to divide a http://psoug.org/definition/ZERO_DIVIDE.htm number by zero. Example Usage: In this example, if the SELECT INTO statement raises a ZERO_DIVIDE exception, the local handler catches it and sets sal_calc to 3000. Execution of the handler is complete, so the sub-block terminates and execution continues with the INSERT statement. DECLARE sal_calc NUMBER(8,2); BEGIN INSERT INTO employees_table VALUES(303, 3000, 0); BEGIN SELECT salary / commission_pct INTO sal_calc FROM employees_table WHERE employee_id = 301; EXCEPTION WHEN ZERO_DIVIDE THEN sal_calc := 3000; END; INSERT INTO employees_table VALUES(304, sal_calc/100, .1); EXCEPTION WHEN ZERO_DIVIDE THEN NULL; END; / Home : Code Library : Sponsors : Privacy : Terms of Use : Contact Us 62 users online © 2009 psoug.org PSOUG LOGIN Username: Password: Forgot your password?
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- want to calculate the male–female ratios for various school clubs, but you discover that the following query fails and issues a divide-by-zero error when http://dbaforums.org/oracle/index.php?showtopic=1818 it tries to calculate ratio for the Lord of the Rings Club, which has no women: SELECT club_id, males, females, males/females AS ratio divide by FROM school_clubs; You can use the function NULLIF to avoid division by zero. NULLIF compares two expressions and returns null if they are equal or the first expression otherwise. Rewrite the query as: SELECT club_id, males, females, males/NULLIF(females, 0) AS ratio FROM school_clubs; Any number divided by divide by zero NULL gives NULL, and no error is generated. Be sure to look back over my posts this week for more SQL tips. And for more on SQL, be sure to check out my book SQL: Visual QuickStart Guide, 3rd Edition. Share|Share on twitter Share on facebook Share on google Share on email✉ Other Things You Might LikeData at Work: Best practices for creating effective charts and information graphics in Microsoft Excel By Jorge CamõesBook $35.99Speaker Camp Workshop: Learn by Video By Russ Unger, Samantha StarmerDVD $39.99Windows 8: Visual QuickStart Guide By Barrie SosinskyBook $27.99 View Your Cart Account Sign InSearchSearch View Your Cart 👤 Account Sign In Create AccountTopicsAdobe PhotoshopAppleDesignDigital PhotographyDigital VideoGadgetsProductivity Web Design & Development StoreBookseBooksVideoWeb EditionsAuthorsSafariExploreAbout UsAcademic ResourcesAffiliatesArticlesBlogsNewslettersPressUser GroupsAboutAffiliatesContact UsJobsLegal NoticePrivacy PolicyPressPromotionsSite MapWrite for Us © 2016 Pearson Education, Peachpit. All rights reserved. 1301 Sansome Street, San Francisco, CA 94111