Below is the control file program I written to load data in a single table with multiple WHEN clause.
Issue is that it is inserting but in a breakdown. Like For insert INTO a table i have written three conditions. It is inserting the all three conditions in a three different rows.
Where I want in single row.
The records are coming for XXSTD_CE_STMNT_HEAD_INT in three diffrent rows. I want in a single row.
Hope I put the query understandable.
--*************************************
-- File Name: XXSTDBANK_CE_STMNT_PKG.ctl
-- Author: Syed Hasan
-- Description: This file will enable SQL*Loader
-- to read a flat file and populate
--************************************
LOAD DATA
INFILE "*"
REPLACE
-- HEADER CONTROL RECORDS FROM SH
INTO TABLE XXSTD_CE_STMNT_HEAD_INT
WHEN (1:2) = 'SH'
TRAILING NULLCOLS
(STMT_STMT_DATE POSITION(3:10) DATE "YYYYMMDD",
STMT_BRANCH POSITION(11:14) CHAR,
STMT_ACC_NO POSITION(15:23) CHAR,
STMT_STMT_NO POSITION(37:39) CHAR,
STMT_PREV_BAL POSITION(40:54) INTEGER EXTERNAL,
STMT_PREV_BAL_DC POSITION(55:55) CHAR)
---HEADER CONTROL RECORDS FROM ST
INTO TABLE XXSTD_CE_STMNT_HEAD_INT
WHEN (1:2) = 'ST'
TRAILING NULLCOLS
(STMT_CURR_BAL POSITION(3:17) INTEGER EXTERNAL,
STMT_CURR_BAL_DC POSITION(18:18) CHAR)
---HEADER CONTROL RECORDS FROM FT
INTO TABLE XXSTD_CE_STMNT_HEAD_INT
WHEN (1:2) = 'FT'
TRAILING NULLCOLS
(STMT_LINE_NO POSITION(3:7),
STMT_TOT_DR_NO POSITION(8:14) CHAR,
STMT_TOT_DR_AMT POSITION(15:29) INTEGER EXTERNAL,
STMT_TOT_CR_NO POSITION(30:36) CHAR,
STMT_TOT_CR_AMT POSITION(37:51) INTEGER EXTERNAL)
-- DETAIL TRANSACTION RECORDS
INTO TABLE XXSTD_CE_STMNT_LINES_INT
WHEN (1:2) = 'TD'
TRAILING NULLCOLS
(STMT_EFF_DATE POSITION(3:10) DATE "YYYYMMDD",
STMT_AMOUNT POSITION(11:25) INTEGER EXTERNAL,
STMT_DR_CR_IND POSITION(26:26) CHAR,
STMT_TRAN_CODE POSITION(56:61) CHAR,
STMT_REFERENCE POSITION(92:121) CHAR)
INTO TABLE XXSTD_CE_STMNT_LINES_INT
WHEN (1:2) = 'SH'
TRAILING NULLCOLS
(STMT_BRANCH POSITION(11:14) CHAR,
STMT_ACC_NO POSITION(15:23) CHAR)