divide by zero error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

divide by zero error

Author

Topic

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 10:35:58

getting that above error when i run this code
INSERT INTO Calamos.ExchangeRates (CMONREF,CODMON,TCOURS,DCTA,DCTA_TCHG)
SELECT Distinct 'USD',ShareCurrency, ROUND(1/ExchanegRate,6),Date,Date
FROM generic.DailyPrice
WHERE ExchanegRate IS NOT NULL

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 10:38:32

That means there are exchange rates that are zero. What do you want to do in those cases? For example, you could make them null - like so:

INSERT INTO Calamos.ExchangeRates (CMONREF,CODMON,TCOURS,DCTA,DCTA_TCHG)
SELECT Distinct 'USD',ShareCurrency, ROUND(1/NULLIF(ExchanegRate,0),6),Date,Date
FROM generic.DailyPrice
WHERE ExchanegRate IS NOT NULL

Or you can choose to exclude such rows, like this:

INSERT INTO Calamos.ExchangeRates (CMONREF,CODMON,TCOURS,DCTA,DCTA_TCHG)
SELECT Distinct 'USD',ShareCurrency, ROUND(1/ExchanegRate,6),Date,Date
FROM generic.DailyPrice
WHERE ExchanegRate IS NOT NULL OR ExchangeRate = 0

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 10:40:15

might make the null all right thanks very much

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 10:41:03

o say a value is 0.72 will that still come back with a divide by error

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-07 : 10:49:08

Value of 0.72 will not cause a divide by zero error.

What is the data type of ExchangeRate column? If it is INT, 0.72 will get truncated to 0. But even in that case, the NULLIF should take care preventing the divide by zero error.

rjhe22
Constraint Violating Yak Guru

283 Posts

Posted - 2013-03-07 : 10:53:45

exchage rate is numeric in table.
that great seem to work perfectly for me thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources