October 21, 2014 at 7:03 am
Being one step removed from innumerate, I was wondering whether there was a more elegant way to avoid divide by zero error instead of trudging through a bunch of isnulls.
My intuition tells me that since multiplication looks like repeated addition, that maybe division is repeated subtraction?
If that's true is there a way to finesse divide by zero errors by somehow reframing the statement as multiplication instead of division?
The sql statement that is eating my kishkas is
cast(1.0*(
(ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/
ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)) as decimal(10,4)) TotalLossRatio
Is there a way to enucleate the error by restating the division? My assertion underlying this statement is that the a alias represents a premium paid, so between medical, pharmacy and dental, there MUST BE at least one premium paid, otherwise you wouldn't be here. the b alias is losses, so likewise, between medical, pharmacy and dental, there MUST BE at least one loss (actually, it just occurred to me that maybe there are no losses, but that would be inconceivable, but ill check again)) so that's when it struck me that maybe there's a different way to ask the question that obviates the need to do it by division.
Clear like mud?
thanks
October 21, 2014 at 7:16 am
Assuming you are serious, yes you can simulate division with subtraction. No, wait! Surely you can't be serious! ("I am serious, and please don't call me Shirley!")
There are basically two ways to avoid divide-by-zero errors:
1. check the denominator before you divide.
2. SET ARITHABORT OFF; http://msdn.microsoft.com/en-us/library/ms190306.aspx
Except, you should never use option 2 in production code for the reasons set out in the article.
Gerald Britton, Pluralsight courses
October 21, 2014 at 7:20 am
Hi Drew,
I get where you're coming from, but those ISNULLs are to prevent incorrect results, as NULL + 5 + NULL = NULL. They won't prevent divide by zero errors.
My solution was always to wrap the denominator with a NULLIF to prevent divide by zero errors, like so:
cast(1.0*(
(ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0))-(ISNULL(b.dnt,0)+ISNULL(b.rex,0)+ISNULL(b.med,0))/
NULLIF(ISNULL(a.DNT,0)+ISNULL(a.rex,0)+ISNULL(a.med,0)), 0) as decimal(10,4)) TotalLossRatio
Readability's a pain but it makes the errors go away!
This will return NULL for anything where the denominator is 0. If you need to return another value such as 0, then the whole lot needs to wrapped in (yet) another ISNULL, sorry 🙂
October 21, 2014 at 7:40 am
Thank you both...I'll go with the nullif option
please forgive my senior moment
I appreciate the help
October 22, 2014 at 12:12 pm
What about [YourNumerator] / NULLIF([YourDivisor], 0) ? If the divisor is zero, it become null, and the result is null. Then handle the null quotient in the method best suited to the circumstances.
October 24, 2014 at 7:37 am
Probably the most straightforward way to prevent divide-by-zero errors is to use a CASE statement. SQL Server SHOULD bypass any divide-by-zero evaluations if you tell it not to attempt to divide by zero (using the WHEN clause.)
October 25, 2014 at 12:44 pm
My most desired unimplemented wish list item is the 'You Know What I Mean' button 😛
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply