| SQL Loader [message #244427] |
Tue, 12 June 2007 14:44 |
Krishna_dev
Messages: 32
Registered: May 2007
|
Member |
|
|
Am loading data into a Headers table and a Lines table. When record_type is 'H' I loaded data into Headers table and when record_type is 'L' data is loaded into lines table. I want to know how to load data where record_type is not 'H' or 'L' for example if the record_type in data file by some mistake is 'X' or 'Z'. These incorrect record_type records will be loaded into Lines table. Below is control file I have built so far.
LOAD DATA
INFILE 'MYDATA.DAT'
APPEND
INTO TABLE HEADERS
WHEN RECORD_TYPE = 'H'
(
STATUS CONSTANT 'L',
RECORD_TYPE POSITION(01:01) CHAR,
FIELD2 POSITION(02:03) CHAR,
FIELD3 POSITION(06:11) CHAR,
FIELD4 POSITION(12:15) CHAR,
FIELD5 POSITION(17:30) CHAR,
FIELD6 POSITION(31:34) CHAR
)
INTO TABLE LINES
WHEN RECORD_TYPE = 'L'
(
STATUS CONSTANT 'L',
RECORD_TYPE POSITION(01:01) CHAR,
FIELD2 POSITION(02:03) CHAR,
FIELD3 POSITION(06:11) CHAR,
FIELD4 POSITION(12:15) CHAR,
FIELD5 POSITION(17:24) CHAR,
FIELD6 POSITION(26:39) CHAR,
FIELD7 POSITION(43:45) CHAR,
FIELD8 POSITION(46:54) CHAR,
FIELD9 POSITION(69:77) CHAR,
)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
