Divide by Zero Error

I have a function that compiles a number of different aspects of a Select query and then creates some high-bred fields based on the result. The problem I have is that some of these values contain a zero or Null and therefore I am getting a divide by zero error. The three sums I have are as follows;

Number 1:-

SELECT........, fnWTRalldata.floortotocc / fnWTRalldata.floortotal AS floorspaceperc, BLAH BLAH FROM

Number 2 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1 AS rentrolldiscperc, BLAH BLAH FROM

Number 3 :-

SELECT........, fnWTRalldata.NetRent / fnWTRalldata.floortotocc AS netrentpersqft, BLAH BLAH FROM

I have been informed that I need to use something like a CASE statement. What I want it to do is that if a ZERO or NULL is detected in any element of the source of the sum, then I want it to ignore the sum and just place the value of zero in whatever the AS xxxxxxxx dictates.

Could someone point me in a direction here or provide me with a little sample of how to go about doing this in a SELECT statement.

Thanks in advance

you can use case like this

select case when fnWTRalldata.floortotal = 0 then 0 else fnWTRalldata.floortotocc / fnWTRalldata.floortotal end as floorspaceperc from table

This should help you avoid division by zero errors.

|||

Thanks for your suggestion Andreas.

In addition to my previous post, would I be better to evaluate these conditions using a CASE statment in a second,third and fourth function and then deliver the results to the afore mention function, or could I use a nested statement within the existing SELECT statements?

Regards

|||

you can also use the iif in reporting services if that helps but it is a pain.

=iif(denominator > 0, numerator/iif(denominator >0, denominator, 1), 0)

|||

This is what I did to solve it. Found the answer elsewhere on the net

SELECT case when floortotal <> 0 then fnWTRalldata.floortotocc / fnWTRalldata.floortotal
else 0 end AS floorspaceperc,
case when FinalRtLsincSC <> 0 then (fnWTRalldata.NetRent / fnWTRalldata.FinalRtLsincSC) - 1
else 0 end AS rentrolldiscperc,
case when floortotal <> 0 then fnWTRalldata.NetRent / fnWTRalldata.floortotal
else 0 end AS netrentpersqft, BLAH BLAH
FROM fnWTRalldata

Thanks for all your suggestions, the solution I found was down the road you were all heading in.

Thanks

Distributor

Hi,
I have set a transactional replication by Interprise
Manager, but when I create a subscriber using "Pull
Subscription to ..." it creates it succesfuly and even
gives me this message that "you have created the
subscription successfully", but it doesn't start
replication and gives this message in "Last Action" column:
" 'XServer' is not Configured as a Distributor. The step
failed".
Thank you very much.
Mathew,
this could be a naming issue.
Please try:
Use Master
go
Select @.@.Servername
This should return your current server name but if it
returns NULL then try:
Use Master
go
Sp_DropServer 'XServer'
GO
Use Master
go
Sp_Addserver 'XServer', 'local'
GO
Stop and Start SQL Services
HTH,
Paul Ibison
|||can the subscriber ping Xserver? Is XServer a Publisher/Distributor or
Distributor?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mathew" <anonymous@.discussions.microsoft.com> wrote in message
news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
> Hi,
> I have set a transactional replication by Interprise
> Manager, but when I create a subscriber using "Pull
> Subscription to ..." it creates it succesfuly and even
> gives me this message that "you have created the
> subscription successfully", but it doesn't start
> replication and gives this message in "Last Action" column:
> " 'XServer' is not Configured as a Distributor. The step
> failed".
> Thank you very much.
>
|||Paul,
Thank you so much. I have checked it, and it's correct.
Actually we have 2 servers in 2 different locations, and
both of them are called 'HAKIM-SERVER', so when I
run "Select @.@.ServerName" they answer 'HAKIM-SERVER'. I
have registered both of them in my "Enterprise
Manager",one by it's own name which is 'HAKIM-SERVER', and
the other one by it's IP address, as we have a VPN. It was
working until 2 weeks ago that our modem and router in the
other building were burned and we had to change them with
a brand new one. Our VPN is fine and there is nothing odd,
but when I go to to "Pull subscription
to 'xxx.xxx.xxx.xxx'... " on the server, which is in our
other location and I registered it with the IP address, to
create the subscribor, it gives me that error message
>--Original Message--
>Mathew,
>this could be a naming issue.
>Please try:
>Use Master
>go
>Select @.@.Servername
>This should return your current server name but if it
>returns NULL then try:
>Use Master
>go
>Sp_DropServer 'XServer'
>GO
>Use Master
>go
>Sp_Addserver 'XServer', 'local'
>GO
>Stop and Start SQL Services
>HTH,
>Paul Ibison
>
>.
>
|||Hi,
It's a Publisher/Distributor. I wrote a complete
explanation for the first respond from Paul. Whould you
please take a look at it?
Thnks again
>--Original Message--
>can the subscriber ping Xserver? Is XServer a
Publisher/Distributor or
>Distributor?
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Mathew" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2c7301c47e2b$46d95c00$a501280a@.phx.gbl...
column:
>
>.
>
|||Mathew,
can you try using an alias rather than an IP address.
Regards,
Paul Ibison
|||Dear Paul,
How can I assign an alias to a remote server when it has
the same name as the local server.
Thanks,

>--Original Message--
>Mathew,
>can you try using an alias rather than an IP address.
>Regards,
>Paul Ibison
>
>.
>
|||Matthew,
in the client network utility, you can add an alias with the TCP/IP network
library. The server alias is any name you choose, and the server name can be
the IP address you have been using.
HTH,
Paul Ibison

Distribution CleanUp creates latency

Hi,
We have one publisher, one distributor and two subscriber. We run
transactional replication.
We recently upgraded all our hardware but now we find ourselves with an
unacceptable latency. When the "Distribution Cleanup" process fires it
take up to 4 minutes to run and will cause a complete replication pause
for 1-2 minutes at times.
In a perfect world, latency would always be under 2 seconds. I'll live
with the very seldom latency of 10 seconds.
What can be done to tame the distribution cleanup jog.
Regards,
CanadianGambler
*** Sent via Developersdex http://www.codecomments.com ***
Nothing. You could run the distribution clean up job nightly and see how the
pooled commands affect performance.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Canadian Gambler" <canadiangambler@.hotmail.com> wrote in message
news:%23xLD5HnsFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We have one publisher, one distributor and two subscriber. We run
> transactional replication.
> We recently upgraded all our hardware but now we find ourselves with an
> unacceptable latency. When the "Distribution Cleanup" process fires it
> take up to 4 minutes to run and will cause a complete replication pause
> for 1-2 minutes at times.
> In a perfect world, latency would always be under 2 seconds. I'll live
> with the very seldom latency of 10 seconds.
> What can be done to tame the distribution cleanup jog.
> Regards,
> CanadianGambler
> *** Sent via Developersdex http://www.codecomments.com ***
|||I guess I should provide more info.
We relocated our equipment room and in the process upgraded the hardware
for all our database servers (1 publisher, 1 distributor and 2
subscribers).
We finished this move 2 weeks ago. The serious latency issue only
started about 6 days ago. We ran the same setup at the previous
location for about 15 months without latency greater than a few seconds.
So something is not quite right. Read on, help is on the way ...
After doing more research last night and this morning we find that some
of the tables that are "suppose to be cleaned up" contain more than
12,000,000 records.
So we are now approaching this problem from a different angle. 1.) Why
are those tables so big and the "clean up" job not "cleaning up". 2.)
How do we go about cleaning those up so that the "clean up job" doesn't
take so long to do it's thing. I saw some similar post so I'm hoping
that I will find some industry wisdom that can help us figure this one
out.
Regards,
Canadian Gambler
*** Sent via Developersdex http://www.codecomments.com ***