Oracle Sql Division By Zero Error
Contents |
Using NULLIF() To Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags: SQL Boyan Kostadinov just sent me a cool link to an article that oracle nullif is the final part in a four part series that discusses the SQL sql divide by null NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth divide by zero error encountered excel part on using NULLIF() to prevent divide-by-zero errors in a SQL call.The idea here is that, as with any other form of math that I know of, you cannot divide by zero in a sql nullif SQL call. Therefore, running this code:
Vertica Nullif
and returns NULL if the two values are the same and can be used to turn the divisor from a zero into a NULL which, in turn, will force the entire equation to become NULL. Therefore, running this code:
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you
Divide By Zero Error Encountered In Stored Procedure
want to calculate the male–female ratios for various school redshift nullif clubs, but you discover that the following query fails and issues a divide-by-zero error when oracle divide by zero function 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 https://www.bennadel.com/blog/984-using-nullif-to-prevent-divide-by-zero-errors-in-sql.htm 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 http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- 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
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 Oracle Scripts Ion Excel-DB Don Burleson Blog http://dba-oracle.com/t_ora_01476_divisor_equa_to_zero.htm
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 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 by zero 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 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 by zero error 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; END; Lastly, you could replace the output of a divide by zero equation with a zero return value: EXCEPTION WHEN ZERO_DIVIDE THEN return 0; END; ��