question

technette avatar image
technette asked

Avoid a Divide by Zero Exception using Select Statement

I have a huge view from which I will be selecting the sum of individual columns, (putting the sum in a report field) then dividing the sum by another field (result will display on report). How do I correctly include a statement that will eliminate the divide by zero exception? The columns are decimal Select sum(column1, column2, column3, column4) as SummarizedName, Column5 From view where parameter1 = @parameter1 and parameter2 = @parameter2 and parameter3 = @parameter3 I don't know how to include a statement that does the division eliminating the error and producing a new value: This may be a poor start... Example: isnull( isnull(SummarizedName,0) / ( nullif(Column5,0)) [NewValuefromDividing] I will eventually turn this into a stored procedure but I need to produce the datasets for now to show the data running through the report correctly.
select
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Slick84 avatar image
Slick84 answered
I usually use a ISNULL(ColumnA, 1) to avoid the divide by zero error.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kevin Feasel avatar image
Kevin Feasel answered
Here's how I tend to handle it: 1 - For summations, include an ISNULL(ColumnA, 0) or COALESCE(ColumnA, 0) statement around each column you are adding. This will prevent problems like what you would get with a "select 3 + NULL" query (or a grouping which has some null values where you were expecting numbers) returning NULL instead of 3. [Code Sample for point 1] select SUM(ISNULL(ColumnA, 0) + ISNULL(ColumnB, 0)) from SomeTableOrView 2 - For the actual division process, do a case statement. [Code Sample for point 2] case when Dividend = 0 then 0 else Divisor / Dividend end as Quotient That way, you won't have null values and won't have a divide by zero problem. You could do an extra when clause in your case statement to handle when Dividend IS NULL, but personally, I'd rather see the NULL come back to let you know that there's a real problem and something got through your error-checking earlier in the process.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you Kevin. I'll try this and get back to you. So this should work even if the data is stored in the view as 0 where there is no value?
0 Likes 0 ·
If 0 means the value 0 and "no value" in your view, then the case statement will return 0 in both circumstances. If there is a business significance to needing to display something different for "there were no records found" than for "the sum of the values which make up the dividend is zero," it might be good to include a count of the number of records and display something in your report if that returns zero.
0 Likes 0 ·
Håkan Winther avatar image
Håkan Winther answered
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette avatar image
technette answered
I was able to use the following: I got this answer form Telerik = Field.A / Field.B = Iif(Field.B = 0, 0, Field.A / Iif(Field.B = 0, 1, Field.B))
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Where do you do this?
0 Likes 0 ·
In SQL Reports, you can create calculated fields on the datasource either in code ASP.NET or in the properties of the datatsource.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.