divide by zero error

How can I avoid this?
I have the following calculations in a view:
Data017_all.MTD_StkOrdAmt / Data017_all.MTD_TotOrdAmt * 100 AS MTD_OSO,
Data017_all.YTD_StkOrdAmt / Data017_all.YTD_TotOrdAmt * 100 AS YTD_OSO,
...if the second number (the _TotOrdAmt) is 0, I get the divide by zero
error. Is there some way to write this so that if that number is 0 the
resulting value is 0 and not an error?
Thanks in advance...>> Is there some way to write this so that if that number is 0 the resulting
Yes, you'd use a CASE expresssion or a combination of NULLIF/COALESCE
functions to work around it. For instance:
SELECT CASE WHEN x = 0 THEN 0 ELSE some_value/x END
Anith|||Try this:
Data017_all.MTD_StkOrdAmt / NULLIF(Data017_all.MTD_TotOrdAmt,0) * 100 AS
MTD_OSO,
Data017_all.YTD_StkOrdAmt / NULLIF(Data017_all.YTD_TotOrdAmt,0) * 100 AS
YTD_OSO,
HTH,
Gert-Jan
r wrote:
> How can I avoid this?
> I have the following calculations in a view:
> Data017_all.MTD_StkOrdAmt / Data017_all.MTD_TotOrdAmt * 100 AS MTD_OSO,
> Data017_all.YTD_StkOrdAmt / Data017_all.YTD_TotOrdAmt * 100 AS YTD_OSO,
> ...if the second number (the _TotOrdAmt) is 0, I get the divide by zero
> error. Is there some way to write this so that if that number is 0 the
> resulting value is 0 and not an error?
> Thanks in advance...|||Try,
...
isnull(Data017_all.MTD_StkOrdAmt / nullif(Data017_all.MTD_TotOrdAmt, 0) *
100, 0)
AS MTD_OSO
...
You can also use a case expression.
...
case when Data017_all.MTD_TotOrdAmt = 0 then 0 else
Data017_all.MTD_StkOrdAmt / Data017_all.MTD_TotOrdAmt * 100 end as MTD_OSO
...
AMB
"r" wrote:

> How can I avoid this?
> I have the following calculations in a view:
> Data017_all.MTD_StkOrdAmt / Data017_all.MTD_TotOrdAmt * 100 AS MTD_OSO,
> Data017_all.YTD_StkOrdAmt / Data017_all.YTD_TotOrdAmt * 100 AS YTD_OSO,
>
> ...if the second number (the _TotOrdAmt) is 0, I get the divide by zero
> error. Is there some way to write this so that if that number is 0 the
> resulting value is 0 and not an error?
> Thanks in advance...
>
>