Home » RDBMS Server » Server Utilities » SQL Loader rounds up to the nearest whole number (Oracle 9i on Solaris 2)
SQL Loader rounds up to the nearest whole number [message #316362] Thu, 24 April 2008 15:45 Go to next message
aliceg
Messages: 29
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 #316367 is a reply to message #316362] Thu, 24 April 2008 16:27 Go to previous messageGo to next message
aliceg
Messages: 29
Registered: July 2007
Location: Los Angeles
Junior Member

I added the precision of 2 decimals to the temp table column that stores the cost. This fixed the issue.
Re: SQL Loader rounds up to the nearest whole number [message #316404 is a reply to message #316362] Fri, 25 April 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
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 Go to previous message
aliceg
Messages: 29
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
Previous Topic: About Export/Import Utility
Next Topic: EXP-00008: ORACLE error 904 encountered, Error while expoerting
Goto Forum:
  


Current Time: Sun Dec 04 04:47:18 CST 2016

Total time taken to generate the page: 0.10730 seconds