0

I have number(20,4) field for which the data is coming in the following format :

120,24
200,45

I want to insert it into a table in the following format :

120.24
200.45

Below is the control file :

LOAD DATA
TRUNCATE INTO TABLE TEMP_TARIFICACION_FIJA_69
FIELDS TERMINATED BY '|' 
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
ID EXPRESSION "TARIFICACION_FIJA_69_SEQ.nextval",
TIPO,
SECTARIFA,
IDFECTAR,
IDGRPDES,
IDCLAISDEST,
IDTIPUSO,
IDTIPHOR,
MONTO "REPLACE (:MONTO,',','.')",
IDOPERADOR,
LAST_MODIFIED_DATE "SYSDATE"
)

I want to replace the field MONTO. I am trying with the above control file but it is not working. How can I achieve this ? Please help me. Thanks in advance.

Mariners
  • 499
  • 8
  • 18
  • Is the original data in a different culture/locale than the desired format? I ask because you may also need to convert 1.120,24 to 1,120.24 (or 1120.24). – Kevin Hogg Apr 23 '14 at 10:16

2 Answers2

0

Can you try this:

monto    "TO_NUMBER(:monto,'999D99', 'NLS_NUMERIC_CHARACTERS='',.''')",
Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
  • Ehh. Sorry. Maybe these examples could help: http://stackoverflow.com/questions/18143888/how-to-use-to-number-and-nullif-in-sql-loader http://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#SUTIL1144 http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_options072.htm#OLADM413 – Lajos Veres Apr 23 '14 at 09:51
0

try this monto "to_number(replace(:nilai_pph,',','.'))"

flyingbird013
  • 446
  • 2
  • 12
  • 28