divide by zero error

Hi, I have a query like this. don't look at from part i have a problem
with the SET part.
UPDATE DSREA
Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) /
ISNULL(ER1.ExchangeRate,1)) * ISNULL(ER2.ExchangeRate,0))), 4) * CASE
DSREA.DebitFlg WHEN 1 THEN 1
ELSE -1
END
FROM DW_Source_RAS_Expense_Addl DSREA(NOLOCK)
INNER JOIN #ExchangeRates ER2(NOLOCK) on DSREA.HostCurrencyCd =
ER2.CurrencyCd
INNER JOIN
(SELECT Currenycd, MAX(ExchangeRateDate) As ExchangeRateDate
FROM ExchangeRates ER ,DW_Source_RAS_Expense_Addl
DSREA(NOLOCK) Where ER.ExchangeRateDate <= DSREA.lockdt
GROUP BY ER.Currenycode
) T2
ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
ER2.CurrencyCd
E.g. DSREA.hostamt is 500
ER1.Exchchangerate is 50
and Er2.echangerate is 80
Now what we are doing is (500 / 50) * 80 = 800
Suppose ER2.Exchnagerate is 0 then (500/50) * 0 = 0
Now the problem is.. the #Exchangerate table is modified. now it
contains the reciprocals of the orinal value for e.g
50 contains 1/50 i.e. 0.02
and 80 contains 1/80 0.0125
So i modified my query to
Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) *
ISNULL(ER1.ExchangeRate,1)) / ISNULL(ER2.ExchangeRate,1))), 4) * CASE
DSREA.DebitFlg WHEN 1 THEN 1
ELSE -1
END
Now everything is working fine except when Er2.exchangrate is 0. if 0
then
(500 * 0.02) / 0
Now i have two problems..
1.It is giving divide by 0 error
2.My Host amount should come as 0 if Er2.exchangerate is 0
How do i achieve it.
Can anyone help me with this ?
Regards,
RajeevHi, Rajeev
Instead of:
[...] SET x=a/b [...]
you can use:
[...] SET x=ISNULL(a/NULLIF(b,0),0) [...]
Razvan|||hI
DECLARE @.d INT
SET @.d=0
SELECT (500 / 50) * CASE WHEN @.d =0 THEN 1 ELSE @.d END
As well you can check fro NULL's
"Rajeev" <rajeev.rajput@.gmail.com> wrote in message
news:1150272582.451330.187380@.h76g2000cwa.googlegroups.com...
> Hi, I have a query like this. don't look at from part i have a problem
> with the SET part.
>
> UPDATE DSREA
> Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) /
> ISNULL(ER1.ExchangeRate,1)) * ISNULL(ER2.ExchangeRate,0))), 4) * CASE
> DSREA.DebitFlg WHEN 1 THEN 1
> ELSE -1
> END
> FROM DW_Source_RAS_Expense_Addl DSREA(NOLOCK)
> INNER JOIN #ExchangeRates ER2(NOLOCK) on DSREA.HostCurrencyCd =
> ER2.CurrencyCd
> INNER JOIN
> (SELECT Currenycd, MAX(ExchangeRateDate) As ExchangeRateDate
> FROM ExchangeRates ER ,DW_Source_RAS_Expense_Addl
> DSREA(NOLOCK) Where ER.ExchangeRateDate <= DSREA.lockdt
> GROUP BY ER.Currenycode
> ) T2
> ON T2.ExchangeRateDate = ER2.ExchangeRateDate AND T2.CurrencyCd =
> ER2.CurrencyCd
> E.g. DSREA.hostamt is 500
> ER1.Exchchangerate is 50
> and Er2.echangerate is 80
> Now what we are doing is (500 / 50) * 80 = 800
> Suppose ER2.Exchnagerate is 0 then (500/50) * 0 = 0
> Now the problem is.. the #Exchangerate table is modified. now it
> contains the reciprocals of the orinal value for e.g
> 50 contains 1/50 i.e. 0.02
> and 80 contains 1/80 0.0125
> So i modified my query to
> Set HostAmt = ROUND(CONVERT(MONEY,(((CONVERT(FLOAT, DSREA.HostAmt)) *
> ISNULL(ER1.ExchangeRate,1)) / ISNULL(ER2.ExchangeRate,1))), 4) * CASE
> DSREA.DebitFlg WHEN 1 THEN 1
> ELSE -1
> END
>
> Now everything is working fine except when Er2.exchangrate is 0. if 0
> then
> (500 * 0.02) / 0
> Now i have two problems..
> 1.It is giving divide by 0 error
> 2.My Host amount should come as 0 if Er2.exchangerate is 0
> How do i achieve it.
> Can anyone help me with this ?
> Regards,
> Rajeev
>|||However... having an ExchangeRate of 0 is usually wrong.
It's better to correct the data and modify the DDL so it doesn't accept
wrong values (i.e. use a check constraint to verify that
ExchangeRate>0). If you need to store rows where the value for the
ExchangeRate is unknown, I think it's better to store NULL in that
column instead of zero.
Razvan