| SQL Loader rounds up to the nearest whole number [message #316362] |
Thu, 24 April 2008 15:45  |
aliceg
Messages: 28
Registered: July 2007
Location: Los Angeles |
Junior Member |
|
|
I am loading an external file using SQL Loader.
The format of the columns in my table are all varchar(x).
One of the columns represents a cost value of a number with 2 decimals. However when the value is for example 15.98 the value loaded in the temporary table comes out to 16. Same holds true for any decimal value that is very close to the whole number.
I would appreciate any suggestions on how to go around this issue.
Thanks, --alice
|
|
|
|
| Re: SQL Loader rounds up to the nearest whole number [message #316404 is a reply to message #316362 ] |
Fri, 25 April 2008 00:37  |
Michel Cadot
Messages: 15241
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
| Quote: | The format of the columns in my table are all varchar(x).
One of the columns represents a cost value of a number with 2 decimals.
|
Wow! you WILL have many problems: performances, inconsistencies, invalid numbers... for sure.
Regards
Michel
|
|
|
| Re: SQL Loader rounds up to the nearest whole number [message #316520 is a reply to message #316362 ] |
Fri, 25 April 2008 08:32 |
joy_division
Messages: 1978
Registered: February 2005
Location: NY |
Senior Member |
|
|
| aliceg wrote on Thu, 24 April 2008 16:45 |
The format of the columns in my table are all varchar(x).
One of the columns represents a cost value of a number with 2 decimals. However when the value is for example 15.98 the value loaded in the temporary table comes out to 16.
|
This of course is impossible. A VARCHAR column does not round values being put into it. How would it round strings that do not look like numbers?
Then...
| Quote: |
I added the precision of 2 decimals to the temp table column that stores the cost
|
How can you add a precision of 2 to a VARCHAR column?
|
|
|
| Re: SQL Loader rounds up to the nearest whole number [message #316551 is a reply to message #316404 ] |
Fri, 25 April 2008 12:11 |
aliceg
Messages: 28
Registered: July 2007
Location: Los Angeles |
Junior Member |
|
|
Michel. I apologize for the post. I wanted to remove once
I realized which was the problem. I then posted that hasty reply and that's where the confusion.
So here we go:
1. The table had the cost column (and some other columns as number format). When I was reading the file I considered however that everything was as Character.
The issue was related to the fact that the number format of the cost column had no specified precision (took it from the mapping indicated by the file generator). This caused the number to be rounded to the next whole.
I then adjusted the precision to 2 (Number(17,2)) and this solved the problem.
Thanks, --Alice
|
|
|
]
Members
Search
Help
Register
Login
Home
