SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 divide by zero error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 03/07/2013 :  10:35:58  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 03/07/2013 :  10:38:32  Show Profile  Reply with Quote
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
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 03/07/2013 :  10:40:15  Show Profile  Reply with Quote
might make the null all right thanks very much
Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 03/07/2013 :  10:41:03  Show Profile  Reply with Quote
o say a value is 0.72 will that still come back with a divide by error
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 03/07/2013 :  10:49:08  Show Profile  Reply with Quote
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.

Go to Top of Page

rjhe22
Constraint Violating Yak Guru

254 Posts

Posted - 03/07/2013 :  10:53:45  Show Profile  Reply with Quote
exchage rate is numeric in table.
that great seem to work perfectly for me thanks
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000