Handling divide by zero 1

[jtrembla]

IN my select statement I have the following line

(dbo.PricingDrugCurrentTherapyDosing.UnitSales / MarketForecaster.SumOfUnitSales( dbo.PricingDrugCurrentTherapyDosing.PricingDrugID, dbo.PricingDrugCurrentTherapyDosing.Country, dbo.PricingDrugCurrentTherapyDosing.BrandGeneric))*100 AS 'Auto-Weights'

What is the best way to handle dividing by zero, also I want the result to be 0 if there is a divide by 0 error.

 

[gmmastros]

Simple, really. Check the denominator. If it's 0, then handle it differently, like this.

Case When MarketForecaster.SumOfUnitSales( dbo.PricingDrugCurrentTherapyDosing.PricingDrugID, dbo.PricingDrugCurrentTherapyDosing.Country, dbo.PricingDrugCurrentTherapyDosing.BrandGeneric) = 0 
     Then 0 
     Else (dbo.PricingDrugCurrentTherapyDosing.UnitSales / MarketForecaster.SumOfUnitSales( dbo.PricingDrugCurrentTherapyDosing.PricingDrugID, dbo.PricingDrugCurrentTherapyDosing.Country, dbo.PricingDrugCurrentTherapyDosing.BrandGeneric))*100 
     End AS 'Auto-Weights'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtrembla]

Thank you, I figured there might have been an equivalent function of NZ() in access.

 

[gmmastros]

I'm not familiar with Access or the NZ function. What does it do? And how would you use it in this query to solve your problem?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Nevermind. I just looked it up.

SQL Server does have an equivalent function named Coalesce.

This function accepts multiple parameters. Each parameter is checked and the first NON-NULL parameter is returned.

Ex:

Select Coalesce(NULL, NULL, NULL, 2)

Will return 2.

I'm not exactly sure how this would help you, but it did lead me to another idea. This approach is probably better than the original advice I gave you because originally, I had you check the value of the function twice, which is less efficient than checking it once. Ya know?

There is another function in SQL Server named NullIf. This function accepts 2 parameters. If the first parameter matches the second, NULL is returned, otherwise, the first parameter is returned.

Ex:

Select NullIf(0,0) [green]-- Returns NULL[/green]
Select NullIf(7,0) [green]-- Returns 7[/green]

We can use a combination of both functions to solve this problem. You see, if you divide by zero, you get an error. But, if you divide by NULL, you get NULL. So, we can use the NullIf to convert the denominator to NULL if it's equal to zero, and then we could coalesce the whole thing, converting it to zero if it's NULL.

Let me show you with an example:

Declare @N1 Decimal(10,2)
Declare @D1 Decimal(10,2)

[green]-- Initialize values, setting denominator to 0[/green]
Select @N1 = 10, @D1 = 0

Select Coalesce(@N1/NullIf(@D1, 0), 0)

Anyway... try this:

[!]Coalesce([/!](dbo.PricingDrugCurrentTherapyDosing.UnitSales / [!]NullIf([/!]MarketForecaster.SumOfUnitSales( dbo.PricingDrugCurrentTherapyDosing.PricingDrugID, dbo.PricingDrugCurrentTherapyDosing.Country, dbo.PricingDrugCurrentTherapyDosing.BrandGeneric)[!], 0), 0)[/!])*100 AS 'Auto-Weights'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[jtrembla]

It doesn't compile, are the parantheses off?

 

[jtrembla]

never mind