How To Avoid Divide By Zero Error In Sql
Contents |
By Chris FehilyJul 25, 2008 Topics: ProductivitySuppose you
Oracle Sql Divide By Zero
want to calculate the male–female ratios for various school divide by zero error encountered excel clubs, but you discover that the following query fails and issues a divide-by-zero error when msg 8134 level 16 state 1 line 1 divide by zero error encountered 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
Nullif Sql
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 Error Encountered In Stored Procedure
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
, Hugo Sombreireiro (@hsombreireiro) , Reto Aeberli (@aeberli) , Steven Peeters (@aikisteve) , Guust Nieuwenhuis (@Lagaffe) , Aurélien Deleusière (@adeleusiere) , and Damien Bruyndonckx (@damienbkx) Using NULLIF() To divide by zero error encountered. the statement has been terminated Prevent Divide-By-Zero Errors In SQL By Ben Nadel on October 3, 2007 Tags:
Divide By 0 In 128 Bit Arithmetic Netezza
SQL Boyan Kostadinov just sent me a cool link to an article that is the final part in a four sql server divide part series that discusses the SQL NULL value. I haven't read the first three parts yet, but there is a really cool tip in the fourth part on using NULLIF() to prevent divide-by-zero http://www.peachpit.com/blogs/blog.aspx?uk=Avoiding-division-by-zero-with-NULLIF-Five-SQL-Tips-in-Five-Days-Part-5- 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 call. Therefore, running this code:
OK, some of the data I am using isnt playing nicely and once in a while I get Divide by zero error encountered. Divide by zero error encountered. Msg 8134, Level 16, State 1, Line 2 To avoid this I am using a CASE as: DECLARE @Int1 https://ask.sqlservercentral.com/questions/22402/best-way-to-avoid-divide-by-zero.html AS INT , @Int2 AS INT SET @Int1 = 6 SET @Int2 = 2 SELECT @Int1 / @Int2 SET @Int1 = 6 SET @Int2 = 0 SELECT @Int1 / CASE WHEN @Int2 = 0 THEN 1 ELSE @Int2 END is there a better way of getting the same results but without littering by nice tidy SELECT statements with hundreds of CASE WHEN ... THEN ... ELSE ... END's?? Am I missing some super useful function that does this in one line? divide by [edit]OK, thanks for the answers so far. I dont want to lose rows where it is zero so, I cant accept a NULL in its place or to filter out those rows with a WHERE @int20 I simply want to replace a 0 value with a 1 value... more ▼ 0 total comments 973 characters / 178 words asked Oct 14, 2010 at 08:54 AM in Default Fatherjack ♦♦ 43.7k ● 79 ● 98 ● 117 edited Oct 14, 2010 at 11:03 divide by zero AM add new comment (comments are locked) 10|1200 characters needed characters left ▼ Everyone Moderators Original poster and moderators Other... Viewable by all users 4 answers: sort voted first ▼ oldest newest voted first 0 You can use NULLIF if you want to return null when division by zero: SELECT @int1 / NULLIF(@int2,0) -- returns NULL or SELECT @int1 / ISNULL(NULLIF(@int2,0),1) -- returns @int1 The CASE statement is probably much faster then the second option, but you can test the performance more ▼ 11 total comments 288 characters / 46 words answered Oct 14, 2010 at 09:04 AM Håkan Winther 16.5k ● 36 ● 45 ● 57 edited Oct 14, 2010 at 09:07 AM Always though about CASE on this one. Good to know about NULLIF Oct 14, 2010 at 09:07 AM ozamora Håkan Winther Nope, the case statement is not any faster because NULLIF is the case statememt. The actuall NULLIF function definition is simply a case statement under the hood, but it has this nice compact form :) nullif (@a, @b) means case when @a = @b then null else @a end Oct 14, 2010 at 09:50 AM Oleg @Håkan Winther Please accept my apologies, It looks like I cannot see straight today, so I did not notice isnull. I do believe though that in this situation the case is going to be only very-very-slightly faster. Oct 14, 2010 at 11:07 AM Oleg @Håkan Winther I did not know h