July 29, 2003 at 10:35 am
Good Afternoon,
I have a vbscript job in a DTS package. It gets data from a Progress database (ODBC and ADO) and inserts it into a SQL Server 7 table (ODBC and ADO). I need to get varchar coming from Progress into a SQL decimal field. The SQL decimal field has a precision = 9 and a scale = 3. I have tried
CONVERT(decimal(9,3),'SOME NUMBER') and CAST('SOME NUMBER' AS decimal(9,3)). Both give an "error converting varchar to numeric". The only way I can get data in is with CONVERT(int,'SOME NUMBER') but this is no good because not all the numbers are integers. I believe varchar to decimal is allowed???
Any ideas???
Thanks!
JM
July 29, 2003 at 10:43 am
QUOTE: Not all the numbers are integers UNQUOTE.
What else can numbers be?
Yes Varchar to decimal is allowed.
BUT! It must be ONLY numbers. No commas, no decimal points.
This can be converted to decimal:
1234
These cannot be converted:
1.234
1,234
-SQLBill
July 29, 2003 at 11:17 am
SQLBill,
QUOTE: Not all the numbers are integers UNQUOTE.
I meant not all values are integer values (1,2,3 ...). There will be 1.15, 2.36 ...
If 1.15 as varchar cannot be converted to 1.15 decimal how can I get 1.15 into a numeric datatype without losing precision?
Thanks for your help!
July 29, 2003 at 11:27 am
Ooppps. I made a mistake.
I just tried this.....
SELECT CONVERT(DECIMAL(9,3), '1.23')
and it returned:
1.230
So the VARCHAR can include the decimal point.
Are you sure there's no other characters (spaces, commas)?
-SQLBill
July 29, 2003 at 11:43 am
Soemthing else to check:
You will get an error if there are too many numbers to the left of the decimal.
For example: DECIMAL(9,3)
Will not accept:
1234567.123
You can only have 6 digits to the left of the decimal since three of the nine digits are required to be to the right of the decimal.
Check that you don't have data with seven or more digits before the decimal.
-SQLBill
July 29, 2003 at 11:51 am
There are no numbers that large.
I just noticed that some records have no value for the field in question. They seem to be coming from Progress as an empty string as opposed to NULL. How would that effect the convert function? Should I convert them to NULL?
Thanks again!
JM
July 29, 2003 at 1:05 pm
As I loop through the recordset, I am checking for empty strings and replacing with NULL. This is then put into the CONVERT(decimal) function. This solved the problem.
Thanks
JM
July 30, 2003 at 5:04 am
You can always convert a varchar field to a decimal field.I have tested this by creating a Test Table with a decimal field with length=5,precision=9,scale=3.
And then running the follwing query:
insert into <test_table>(<col_name>)values
(CAST('121212.3' AS decimal(9,3)))
In this case 121212.3 is a varchar value.And it is working fine.I think it will help you to find the exact cause of your problem.
July 30, 2003 at 6:33 am
Thanks for your checking!
It appears my problem was with empty strings. Converting them to NULL allowed the CONVERT function to deal with them.
You are correct, varchar can be converted to decimal but watch out for empty strings.
JM
July 30, 2003 at 8:47 am
One way to go around this problem is to get the number directly from Progress as opposed to you converting it. You can use To_NUMBER Progress function. The function will convert the Char_expression to a number. for example: you can have the following:
SELECT *
FROM customer
WHERE TO_NUMBER (SUBSTR (phone, 1, 3)) = 603 ;
I hope this helps.
Ahmad Khashan
July 30, 2003 at 10:54 pm
Yeah.. You have to check first whether the string is empty or not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply