| How to load in number to number with a decimal point [message #367997] |
Tue, 14 September 1999 06:10  |
arjan
Messages: 6
Registered: September 1999
|
Junior Member |
|
|
Asci file is like this :
blablabla 001275 003500 004500
(the space between the amounts is positive a - is negative amount)
The problem is how to get the numeric fields into oracle.
They have to be in Oracle as
blabla etc 12.75 35.00 45.00
I have tried to do it like this :
BEDRAG_AV POSITION(88:94) ":BEDRAG_AV/100",
BEDRAG_AV_A POSITION(95:101) ":BEDRAG_AV_A/100",
BEDRAG_AV_TU POSITION(102:108) ":BEDRAG_AV_TU/100",
BEDRAG_AV_G POSITION(109:115) ":BEDRAG_AV_G/100",
BEDRAG_ZFW POSITION(116:122) ":BEDRAG_ZFW/100",
BEDRAG_VR POSITION(123:129) ":BEDRAG_VR/100",
BEDR_O_P POSITION(130:136) ":BEDR_O_P/100",
TOTAAL POSITION(137:144) ":TOTAAL/100"
but the problem is that the amount was rounded up
from 12.75 to 13.00
How to do this ?????????????
(Excuse me for the language)
Arjan
|
|
|
|
| Re: How to load in number to number with a decimal point [message #367998 is a reply to message #367997] |
Wed, 15 September 1999 12:38  |
hmg
Messages: 40
Registered: March 1999
|
Member |
|
|
I have trying your example with
a) the ctl-File
load data
infile 'd:\temp\loader\test.txt'
badfile 'd:\temp\loader\test.bad'
discardfile 'd:\temp\loader\test.dsc'
replace
into table numtab
(col1 POSITION(1:7) ":col1/100",
col2 POSITION(8:14) ":col2/100",
col3 POSITION(15:21) ":col3/100" )
b) the datafile test.txt
001275 003500 004500
-001675 003560-074502
c) and this structure of table numtab
SQL> desc numtab
Name Null? Type
------------------------------- -------- ----
COL1 NUMBER(10,2)
COL2 NUMBER(10,2)
COL3 NUMBER(10,2)
I get and the following result:
SQL> select * from numtab;
COL1 COL2 COL3
---------- ---------- ----------
12.75 35 45
-16.75 35.6 -745.02
Could it be that the columns in your table are defined as NUMBER(10) instead of NUMBER(10,2) ?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
