Home » RDBMS Server » Server Utilities » SQL Loader TRIM in WHEN clause (Oracle 11G)
SQL Loader TRIM in WHEN clause [message #550447] Mon, 09 April 2012 09:38 Go to next message
sinpeak
Messages: 55
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 #550453 is a reply to message #550447] Mon, 09 April 2012 11:10 Go to previous messageGo to next message
Littlefoot
Messages: 21528
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As WHEN clause doesn't allow use of functions, I believe that you'll have to switch to external tables which are much more flexible.
Re: SQL Loader TRIM in WHEN clause [message #550464 is a reply to message #550447] Mon, 09 April 2012 12:32 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
What if you make a second WHEN clause for the other case of
" D "
Re: SQL Loader TRIM in WHEN clause [message #550465 is a reply to message #550464] Mon, 09 April 2012 12:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8919
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 '"'
)

Re: SQL Loader TRIM in WHEN clause [message #550489 is a reply to message #550465] Mon, 09 April 2012 14:23 Go to previous messageGo to next message
Littlefoot
Messages: 21528
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, yes, if
"D" and " D "
are the only possible values. What if there are
"D"
" D"
" D "
"  D"
"    D  "
Re: SQL Loader TRIM in WHEN clause [message #550494 is a reply to message #550489] Mon, 09 April 2012 15:24 Go to previous message
Barbara Boehmer
Messages: 8919
Registered: November 2002
Location: California, USA
Senior Member
It will work for no leading spaces or one leading space, with or without any number of trailing spaces. If there can be more than one leading space, then you need to add more when clauses.

Previous Topic: Loading Multiples excel files to multiple tables
Next Topic: Daily Base Back up file
Goto Forum:
  


Current Time: Sat Jan 18 01:18:12 CST 2020