| 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: 9104
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 '"'
)
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
