Tuesday, 4 December 2018

Concatenate fields and populate into single column in table using SQLLDR Control file


Recently we got a requirement where in we get a chart of accounts mapping file from legacy to EBS. Due to some legacy issues, we ended up using SQLLDR and Control file approach to load into EBS tables. The data file has  4 legacy segments and corresponding 8 EBS segments (Our client uses 8 segments) something like below

LS -- Legacy Segments
EBS -- EBS Segments

LS1, LS2, LS3, LS4 -->  EBS1.. EBS8


Table structure:
LS_CodeCombination  | EBS_CodeCombination
LS1.LS2.LS3.LS4      | EBS1.EBS2...EBS8

We used BOUNDFILLER to define filler columns in Control file.

OPTIONS (SKIP =1)
LOAD DATA
APPEND
INTO TABLE XX_LS_CC_EBS_CC_STG
Fields terminated by "," Optionally enclosed by '"'
TRAILING NULLCOLS
(
LS1 BOUNDFILLER,
LS2 BOUNDFILLER,
LS3 BOUNDFILLER,
LS4 BOUNDFILLER,
EBS1 BOUNDFILLER,
EBS2 BOUNDFILLER,
EBS3 BOUNDFILLER,
EBS4 BOUNDFILLER,
EBS5 BOUNDFILLER,
EBS6 BOUNDFILLER,
EBS7 BOUNDFILLER,
        EBS8 BOUNDFILLER,
LEGACY_CC "'Account Combination: '||:LS1 ||'.'||:LS2 ||'.'||:LS3 ||'.'||:LS4",
ORACLE_CC "TRIM(:EBS1||'.'||:EBS2||'.'||:EBS3||'.'||:EBS14|'.'||:EBS5)" );

No comments:

Post a Comment