How To Avoid Divide By Zero Error In Sybase
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you oracle sql divide by zero might have Meta Discuss the workings and policies of this
Oracle Divide By Zero Exception
site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or oracle nullif posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community vertica nullif 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 favorite 7 SELECT YEAR, period, round((1- sum(rej_qty) / sum(recd_qty))*100, 0) FROM TAB_A WHERE sid = '200' AND sdid IN
Oracle Divide By Zero Function
('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 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,283226795 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 b
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 how to avoid divide by zero error in sql clubs, but you discover that the following query fails and issues a divide-by-zero error when
Oracle Sql Divisor Is Equal To Zero
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 http://stackoverflow.com/questions/5027808/how-to-avoid-divide-by-zero-error-in-an-sql-query 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
up partitions Grid Computing View All Logical Partitions (LPAR) View All AIX on iSeries Linux on iSeries Setting up partitions Windows on iSeries On-demand Computing View All http://search400.techtarget.com/tip/Avoid-divide-by-zero-in-query-and-SQL Autonomic Computing Capacity on Demand Please select a category Grid Computing Logical https://www.experts-exchange.com/questions/27491656/Divide-by-zero-occurred-on-Sybase-15-0-3.html Partitions (LPAR) On-demand Computing Section Problem Solve News Get Started Evaluate Manage Problem Solve Sponsored Communities Avoid "divide by zero" in query and SQL byFred Wagaman Here's a simple workaround to avoid the "divide by zero" error. In this Article Share this item with your network: divide by Related Content Using ISNULL or NULLIF to prevent division by zero – SearchOracle A scalar subquery – SearchOracle Performing INSERT calculations – SearchOracle Sponsored News SAP HANA in action – learning lessons from early adopters –IBM Hosted Microsoft® Productivity & Collaboration Tools Help Deliver Greater ... –Rackspace See More Vendor Resources Sybase IQ "Quick Start Guide" –Sybase, an SAP divide by zero company eGuide: Performance tuning SQL Server via queries, indexes and more –Microsoft Many times when doing mass updates or query reports, you run into situations where a "divide by zero" error occurs.... Sign in for existing members Continue Reading This Article Enjoy this article as well as all of our content, including E-Guides, news, tips and more. Step 2 of 2: You forgot to provide an Email Address. This email address doesn’t appear to be valid. This email address is already registered. Please login. You have exceeded the maximum character limit. Please provide a Corporate E-mail Address. By submitting my Email address I confirm that I have read and accepted the Terms of Use and Declaration of Consent. By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.
for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Ask a Question Ask for Help Receive Real-Time Help Create a Freelance Project Hire for a Full Time Job Ways to Get Help Expand Search Submit Close Search Login Join Today Products BackProducts Gigs Live Careers Vendor Services Groups Website Testing Store Headlines Experts Exchange > Questions > "Divide by zero occurred" on Sybase 15.0.3 Want to Advertise Here? Solved "Divide by zero occurred" on Sybase 15.0.3 Posted on 2011-12-13 Sybase Database 1 Verified Solution 2 Comments 1,694 Views Last Modified: 2012-05-12 Dears, I got the following error while executing a pocedure that do some calculations on "Sybase 15.0.3": 1 36 0 Number (3607) Severity (16) State (0) Server (instance name) Procedure (procedure name) Divide by zero occurred. (22012) While the same procedure run successfully on "Sybase 12.5.3" without any errors! This procedure use "set arithabort numeric_truncation off" before the calculation start. So is there any difference in using the "set arithabort numeric_truncation" between Sybase 12.5.3 & 15.0.3 Thanks in advance, Ramez Nabil 0 Question by:Ramez Facebook Twitter LinkedIn Google LVL 2 Best Solution bymaneksh Hi Looks like there is a difference in behaviour . I did a small test ##### 12..5 ############################## 6> select @@version 7> go ---------------------------------------------------------------------------------------------------------------------------- Go to Solution 2 Comments LVL 2 Overall: Level 2 Sybase Database 2 Message Accepted Solution by:maneksh2011-12-14 Hi Looks like there is a difference in behaviour . I did a small test ##### 12..5 ############################## 6> select @@version 7> go ---------------------------------------------------------------------------------------------------------------------------- Adaptive Server Enterprise/12.5.3/EBF 12593 ESD#3/P/Sun_svr4/OS 5.8/ase1253/1911/64-bit/FBO/Fri Jul 8 13:31:40 2005 (1 row affected) 1> drop proc p 2> go 1> 2> create procedure p as 3> begin 4> declare @a numeric (9,5),@b int 5> select @a =122.533,@b =0 6> select @a/@b 7> end 8> go 1> p 2> go ----------------------- (return status = 0) Divide by zero occurred. 1> ####