Divide By Zero - How to prevent

Gentle / Ladies
I am converting over a computer system from HQL to SQL and it has some
statements that I need to convert.
If you look at the below line of HQL code you will see the 'greatest'
function which as used prevents the divide by zero error under HQL. It works
on the absolute value of the second parameter so ( 100 - CurrentMargin(value
of 125) ) would equal 25 for the comparison of which is greater 1 or 25 but
does not impact the true value of -25. The main purpose is if the
CurrentMargin were to equal 100, subtracting it from 100 giving a value of 0
thus the greatest function would return a 1.
Select
ItemNum,
(CurrentMargin / greatest(1, (100 - CurrentMargin)) ) * InventoryTurns as
ROII
How can I rewrite it so that it prevents the divide by zero error if the
CurrentMargin is 100.
Sorry if I rambled on a bitMark,
You can use the SQL CASE expression:
select
ItemNum,
(
CurrentMargin
/
CASE WHEN 100-CurrentMargin < 1 THEN 1 ELSE 100 - CurrentMargin END
) * InventoryTurns as ROII
...
Steve Kass
Drew University
Mark Moss wrote:

>Gentle / Ladies
>
>I am converting over a computer system from HQL to SQL and it has some
>statements that I need to convert.
>If you look at the below line of HQL code you will see the 'greatest'
>function which as used prevents the divide by zero error under HQL. It work
s
>on the absolute value of the second parameter so ( 100 - CurrentMargin(valu
e
>of 125) ) would equal 25 for the comparison of which is greater 1 or 25 but
>does not impact the true value of -25. The main purpose is if the
>CurrentMargin were to equal 100, subtracting it from 100 giving a value of
0
>thus the greatest function would return a 1.
>Select
>ItemNum,
>(CurrentMargin / greatest(1, (100 - CurrentMargin)) ) * InventoryTurns as
>ROII
>How can I rewrite it so that it prevents the divide by zero error if the
>CurrentMargin is 100.
>Sorry if I rambled on a bit
>
>|||As I understand, if the CurrentMargin is 99.5, you do not really want
to divide by 1 (you want do divide by 100-99.5). In this case, you
might try:
SELECT ItemNum,
CurrentMargin/NULLIF(100-CurrentMargin,0)*InventoryTurns
...
or:
SELECT ItemNum,
CurrentMargin/ISNULL(NULLIF(100-CurrentMargin,0),1)*InventoryTurns
...
Razvan

Distributor and publisher

Hi All:
i have two publications in my local computer, then i go to
distributor tab in publisher and distributor properties ,which shows two
distributor database there. that make sense coz i have two publication one
for each. is that right?
my question is why in the publisher tab of publisher and distributor
properties only has one distributorDB ?
Cheers
nick
I don't think so. Each publisher can use a single distribution database.
However you can have one or more distribution database per server.
So consider three publishers a, b, and c. Publisher a and publisher b use
the distribution database d1 on publisher c. Publisher c used a local
distribution database on publisher c called distribution. You could also
configure publisher a to use d2 (another distribution database) on publisher
c and publisher b could still use d1 on publisher c.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>
|||Nick,
Hilary is correct, but what you are thinking of is
something I also assumed existed when I first saw the
option of several distribution databases. I still think
this would be a good idea as disaster recovery is far
more versatile if the msrepl_commands table could be
specific to a publication rather than a publisher ie if
one publisher has 2 transactional publications, they'd go
into separate distribution databases. Unfortunately this
would require a huge amount of coding to amalgamate
different notification data so I guess it's not going to
happen soon.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

>--Original Message--
>I don't think so. Each publisher can use a single
distribution database.
>However you can have one or more distribution database
per server.
>So consider three publishers a, b, and c. Publisher a
and publisher b use
>the distribution database d1 on publisher c. Publisher c
used a local
>distribution database on publisher c called
distribution. You could also
>configure publisher a to use d2 (another distribution
database) on publisher
>c and publisher b could still use d1 on publisher c.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>Now available on Amazon.com
>http://www.amazon.com/gp/product/offer-
listing/0974973602/ref=dp_more-buying-choices_2//102-
1802128-2428137?condition=all[vbcol=seagreen]
>Looking for a FAQ on Indexing Services/SQL FTS
>http://www.indexserverfaq.com
>"Nick" <fsheng@.ebreathe.co.nz> wrote in message
>news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
computer, then i go to[vbcol=seagreen]
properties ,which shows two[vbcol=seagreen]
two publication one[vbcol=seagreen]
publisher and
>distributor
>
>.
>
|||Thanks Hilary and Paul
so in this case, two publication are sharing one distribution DB.
Cheers
nick
"Nick" <fsheng@.ebreathe.co.nz> wrote in message
news:O7ceZ4kCFHA.2568@.TK2MSFTNGP10.phx.gbl...
> Hi All:
> i have two publications in my local computer, then i go to
> distributor tab in publisher and distributor properties ,which shows two
> distributor database there. that make sense coz i have two publication one
> for each. is that right?
> my question is why in the publisher tab of publisher and
distributor
> properties only has one distributorDB ?
>
> Cheers
> nick
>

Distribution Problem

I have a crystal reports porject that runs great on my computer, but when I put it on another computer, it has an unhandled exception. It won't run on any computer that doesn't have visual studio installed. What do I do to fix this?You have to search for a list of the dlls that need to be installed. CR 8.5 came with a file called runtime.hlp. If you can't find that file, try Crystal's website:
http://support.businessobjects.com/search/advsearch.asp

Distributing SQLServer Database

Good Day!

I want to know how to distribute a database in SQL Server from one computer to another?

Thnx!

Kapalic

you can use Backup/restore method or dettach /attach method. Backup/restore is an online metod and dettach/attach is an offline method.

Madhu