Home » RDBMS Server » Server Utilities » How to load in number to number with a decimal point
How to load in number to number with a decimal point [message #367997] Tue, 14 September 1999 06:10 Go to next message
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 Go to previous messageGo to next message
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) ?
Re: How to load in number to number with a decimal point [message #368001 is a reply to message #367998] Mon, 20 September 1999 07:28 Go to previous message
arjan
Messages: 6
Registered: September 1999
Junior Member
Thanks HMG for your response.
Yes the problem (big mistake) was in the table
It was defined as number(10,0) in stead of (10,2)
(the table was delivered by someone else but i had to see it.

Thanks for thinking with me

Arjan
Previous Topic: While you're working hard on Oracle stuffs ...
Next Topic: Direct Path Homogeneity Error
Goto Forum:
  


Current Time: Mon Jun 17 08:27:57 CDT 2024