SQL Loader TRIM in WHEN clause [message #550447] |
Mon, 09 April 2012 09:38 |
|
sinpeak
Messages: 59 Registered: January 2011 Location: india
|
Member |
|
|
Hi, I am new to this forum and SQL Loader.
My control file is :
LOAD DATA
APPEND
INTO TABLE IPGITLREDATA WHEN ITL_REC_TYPE = 'D'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ITL_REC_TYPE CHAR,
ITL_RE_ID INTEGER EXTERNAL,
START_DATE DATE "YYYYMMDD" "TRIM(:START_DATE)",
END_DATE DATE "YYYYMMDD",
COST_BAND_NAME CHAR OPTIONALLY ENCLOSED BY '"',
)
The data file might have a value of " D " instead of "D" for ITL_REC_TYPE and ITL_REC_TYPE is in the WHEN clause. How can I check for the trimmed value of ITL_REC_TYPE in the WHEN clause ?
Thanks.
|
|
|
|
|
Re: SQL Loader TRIM in WHEN clause [message #550465 is a reply to message #550464] |
Mon, 09 April 2012 12:39 |
|
Barbara Boehmer
Messages: 9097 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
LOAD DATA
APPEND
INTO TABLE IPGITLREDATA
WHEN ITL_REC_TYPE = 'D'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ITL_REC_TYPE CHAR "LTRIM(RTRIM(:ITL_REC_TYPE))",
ITL_RE_ID INTEGER EXTERNAL,
START_DATE DATE "YYYYMMDD" "TRIM(:START_DATE)",
END_DATE DATE "YYYYMMDD",
COST_BAND_NAME CHAR OPTIONALLY ENCLOSED BY '"'
)
INTO TABLE IPGITLREDATA
WHEN ITL_REC_TYPE = ' D'
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
ITL_REC_TYPE POSITION(1) CHAR "LTRIM(RTRIM(:ITL_REC_TYPE))",
ITL_RE_ID INTEGER EXTERNAL,
START_DATE DATE "YYYYMMDD" "TRIM(:START_DATE)",
END_DATE DATE "YYYYMMDD",
COST_BAND_NAME CHAR OPTIONALLY ENCLOSED BY '"'
)
|
|
|
|
|