CODEWITHSUNDEEP
oracle-databasesql-loader
Vikram Kanse
Vikram Kanse

Reputation: 11

How to perform an add functionality in sql loader file

I have a fixed length data file a.dat with below data in it

1234544550002200011000330006600000

my focus is on specific positions

POSITION(1:4)
POSITION(5:8)

and I want to add values in these 2 positions and insert it in a field named Qty in XYZ_Table.

I am trying to the following in my CTL file. But it fails, and I don't know how to pursue it further. 

LOAD DATA
 INFILE '$SOME_DATA/a.dat'
 APPEND
 PRESERVE BLANKS
 INTO TABLE XYZ_Table
(QTY   POSITION(1:4)+POSITION(5:8)  "to_number(:QTY)")

I need to achieve this addition functionality in SQL Loader only.

If the above methodology is not possible, it would be great if you can help me with a different approach.

P.S: What I am trying to achieve is just one part of the bigger CTL file.

Upvotes: 1

Views: 1123

Answers (1)

Gary_W
Gary_W

Reputation: 10360

You need to identify the positions you want to add together but not load into their own columns as "BOUNDFILLER", which means don't load them but remember them for use in an expression later. Then use like this:

LOAD DATA
infile test.dat 
append
preserve blanks
INTO TABLE X_test
TRAILING NULLCOLS
(val_1  BOUNDFILLER position(1:4)
,val_2  BOUNDFILLER position(5:8)
,qty    ":val_1 + :val_2"
)

Upvotes: 2

Related Questions