Home » RDBMS Server » Server Utilities » sqlldr using decode for converting amount fields
sqlldr using decode for converting amount fields [message #408776] Wed, 17 June 2009 15:18 Go to next message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
My Source .dat file (having positional data) contains around 25 amount fields. For each amount field,if the sample data is like
DUE_AMT = 1023456789
DUE_AMT_SGN = -
then the data expected in target in
DUE_AMT = -10234567.89

One way is loading the data as is in the target and then transforming and updating the records (like decode(DUE_AMT_SGN,'-',DUE_AMT*-0.01,DUE_AMT/100).
Is it possible to transform in my control file directly while loading so that I dont need to do any updates after loading.When I am trying to use the decode statement in my control file, it says the column is not allowed here. Any suggestions?

Thanks!
Re: sqlldr using decode for converting amount fields [message #408785 is a reply to message #408776] Wed, 17 June 2009 15:46 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You've probably missed the colon (:) sign; here's an example. Table to be loaded:
SQL> create table test (due_amt number, due_amt_sgn varchar2(1));

Table created.

SQL> 

Control file (which contains some sample data):
load data
  infile *
  replace
into table test
  fields terminated by ','
  trailing nullcols
(due_amt "decode(:due_amt_sgn, '-', -:due_amt, :due_amt)",
 due_amt_sgn
)

begindata
100,+
200,-
300,-
400,+

Loading session:
SQL> $sqlldr scott/tiger control=test.ctl log=test.log

SQL*Loader: Release 10.2.0.1.0 - Production on Sri Lip 17 22:43:14 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 3
Commit point reached - logical record count 4

Checking what has been done:
SQL> select * from test;

   DUE_AMT D
---------- -
       100 +
      -200 -
      -300 -
       400 +

SQL> 
Re: sqlldr using decode for converting amount fields [message #408796 is a reply to message #408785] Wed, 17 June 2009 17:57 Go to previous message
nidi_03
Messages: 10
Registered: May 2009
Location: US
Junior Member
yes, I did miss the colon Embarassed
Thanks for your response!
Previous Topic: sqlldr - exit error code 2 in unix (merged)
Next Topic: issue with import (merged 2) 10.1.0.2.0 windows
Goto Forum:
  


Current Time: Fri Mar 29 10:17:17 CDT 2024