Hi all,
I am trying to create an external table which loads into different columns based on different conditions.
I am looking to mimic SWIFT940.ctl using external tables.
Could you please share relevant examples of using multiple LOAD WHEN clause?
LOAD DATA
REPLACE
PRESERVE BLANKS
INTO TABLE ce_stmt_int_tmp
--------------------------------------------
WHEN rec_id_no = ':20'
TRAILING NULLCOLS
(rec_no RECNUM,
rec_id_no POSITION(1:3) CHAR
TERMINATED BY WHITESPACE "ltrim(:rec_id_no, ':')",
column1 POSITION(5:85) CHAR)
INTO TABLE ce_stmt_int_tmp
--------------------------------------------
WHEN rec_id_no = ':60'
TRAILING NULLCOLS
(rec_no RECNUM,
rec_id_no POSITION(1:3) CHAR
TERMINATED BY WHITESPACE "ltrim(:rec_id_no,':')",
column1 POSITION(4:4) CHAR,
column2 POSITION(6:6) CHAR,
column3 POSITION(7:12) CHAR,
column4 POSITION(13:15) CHAR,
column5 TERMINATED BY WHITESPACE "replace(decode(:column2, 'D', '-'||:column5, :column5), ',', '.') ")
I am able to use a single LOAD WHEN condition:
CREATE TABLE test8 (
REC_NO NUMBER,
REC_ID_NO VARCHAR2(30 BYTE),
COLUMN1 VARCHAR2(2000 BYTE))
ORGANIZATION EXTERNAL
( type oracle_loader
default directory "MT940_CURRENT"
access parameters
( records delimited BY newline
LOAD WHEN
(
rec_id_no = ":20"
)
FIELDS
(rec_no RECNUM,
rec_id_no POSITION(1:3) CHAR
TERMINATED BY WHITESPACE,
column1 POSITION(5:85) CHAR)
)
location ('file.csv')
);