SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting a #Error with Expression When Dividing 0 by 0


Getting a #Error with Expression When Dividing 0 by 0

Author
Message
rdsb_2170
rdsb_2170
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 78
Greeting,

I have the following expression that is return #Error when the data contains a 0/0. I have tried several changes to the expression, no of which worked - UGH. Any suggestions would be helpful.

= IIF(Fields!Presentations.Value = 0, "00:00:00", Format(DateAdd("s", (Fields!TalkTimeSeconds.Value/Fields!Presentations.Value), "00:00:00"), "HH:mm:ss"))

Thank you,
Cervello

bmg002
bmg002
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14787 Visits: 2433
Hello,

I have a thought on this. Is it an error when Presentations has a value of 0 or when it is null? You may want to also check for null values as dividing by null I think will give you an #Error as well.
The other thing I can think of is what does the error window say? It usually tells you what was wrong if something is wrong.

For debugging purposes, you could change your IIF to:
=IIF(Fields!Presentations.Value=0,0,1)

that way you can determine what is wrong. It will tell you which rows are seeing the value as 0 and what rows are seeing it as non-zero.
Also, what is the datatype on Presentations? If it is not an int or a derivitive of int, comparing it to the integer 0 will return false I believe. That is, I am fairly confident that SSRS sees 0 and "0" (integer vs string) as different values.
rdsb_2170
rdsb_2170
SSC-Enthusiastic
SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)SSC-Enthusiastic (171 reputation)

Group: General Forum Members
Points: 171 Visits: 78
Thank you for getting back to me.
I found the solution and wanted to share in case any one else comes across this issue.

use the following code resolves the issue of dividing by zero OR dividing zero by zero.

=IIF(Fields!Presentations.Value > 0, Format(DateAdd("s", (Fields!TalkTimeSeconds.Value/
IIF(Fields!Presentations.Value > 0, Fields!Presentations.Value,1)),"00:00:00"),"HH:mm:ss"), "00:00:00")

Thank you,
Cervello
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search