Divide By Zero

Question

Post reply

Divide By Zero

Simon Young

SSC Enthusiast

Points: 115
More actions
February 23, 2006 at 11:07 am

#111270

Hi
I am using the following code in part of a SELECT statement:
CAST((
(
CAST(IsNull(MonthlyBookings,0) AS DECIMAL(18,2))
/
CAST(IsNull(CountEnqByUser,0) AS DECIMAL(18,2))
) * 100
)AS DECIMAL(8,1)) AS ConversionRate
..to calculate a conversion rate. This is time period dependent and so when a short time period is used the CountEnqByUser value is often 0 causing a Divide By Zero error.
What is the best way to approach this so that I can simply write the ConversionRate figure out as a 0 if the CountEnqByUser figure is zero to avoid the error?
Thanks in advance for any assistance offered...
Regards
Simon

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Use CASE ... WHEN to translate a zero divisor to a zero output ...

CASE

WHEN IsNull(CountEnqByUser,0) = 0 THEN 0

ELSE

CAST(IsNull(MonthlyBookings,0) AS DECIMAL(18,2))

/

CAST(IsNull(CountEnqByUser,0) AS DECIMAL(18,2))

END

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Why ISNULL???

You are creating problems for yourself!

Move in the opposite direction:

ISNULL(CAST(IsNull(MonthlyBookings,0) AS DECIMAL(18,2))

/

CAST(NULLIF(CountEnqByUser,0) AS DECIMAL(18,2)) , 0)

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

And do roundings AFTER calculations, not before!

ISNULL(CAST(IsNull(MonthlyBookings,0) / NULLIF(CountEnqByUser,0) AS DECIMAL(18,2)) , 0)

Oterwise you lose precision.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

Right on, Serqiy...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)