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