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.
| Author |
Topic |
|
jjasper
Starting Member
25 Posts |
 Posted - 2008-04-03 : 15:00:59
|
| I have 2 fields that are both unfortunately formatted as characters. They are numbers that look like 1.5, 2, .3, etc... Could you tell me the best way to perform division on them within a SQL query? - should I cast as decimal? Is that the correct type ?Thank you |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-03 : 15:22:31
|
| The best thing to do is store the values in columns with the appropriate datatype.But to do the math, yes, convert or cast the value to some numeric type of datatype (decimal, numeric, money, etc). Likely problems will be if any of the value won't convert because of invalid characters and of course you should protect from devide by zero errors.Be One with the OptimizerTG |
 |
|
|
jjasper
Starting Member
25 Posts |
Posted - 2008-04-03 : 15:58:31
|
| Going with that thought: "The best thing to do is store the values in columns with the appropriate datatype."How I got these in the first place was importing them from a CD-ROM using an ODBC connector with a DSN of a textdriver (it was a .txt file).Now when I try to import these 3 million records, I get errors on the import because inevitably, the company sending the data usually gets some rows screwed up - so it seems my only option is to get them all in there as text...So, would you then recommend I try to convert the ones I can into another table with correct datatypes or just struggle with the "cast" stuff? To me, it seems like "6 of 1, half dozen of the other" -- but if I am doing it wrong please tell me, I want to learn doing it the best way.... |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-03 : 16:06:41
|
| That type of "feed" usually goes into a "staging" table to get all rows no matter what. Then a controlled transormation process can load your properly desinged tables. "error" rows can be discarded or logged or alerted on or whatever. But don't let the import process prevent you from well designed database.Be One with the OptimizerTG |
|
|
|
jjasper
Starting Member
25 Posts |
Posted - 2008-04-03 : 16:19:10
|
| Would love to understand creating "a controlled transormation process" Is this done through DTS? or is it queries that the admin designs himself? I honestly could never get past a bad field, when trying to import or convert, it just seems to fail if it comes across a datatype mismatch.John |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-04-03 : 17:25:30
|
| sorry - I typo'd "transformation"It could be a DTS package, it could be a t-sql script. Anything that would insert your data from the staging table to the real table performing the appropriate CONVERTs. By "controlled" I mean once you have the data in sql server you have complete control over the transformation process. Through a t-sql script you can insert only the rows that you know can convert successfully. you can choose what to do with the rows (or individual values) that won't convert. T-Sql has a lot of functions that can help but if you have no experience with it it could be a challenge.Be One with the OptimizerTG |
|
|
|
|
|
|
|
|
need to divide 2 char fields