Re: SQL*Loader question- Transforming Data

You could try:

when (9:12) <> 'UNIX'

However, this will only work if you have fixed field lengths in the input file.

Jonathan Gennick wrote:

> David,
>
> SQL*Loader probably can't help here, because, unfortunatly,
> the WHEN clause is not nearly as flexible as a SQL WHERE
> clause. If no other solution presents itself, you could load
> the data into a work table and write some PL/SQL code to
> filter out the records that you don't want. You're better
> of, though, if you can preprocess the file and remove the
> records you don't want before you even load the data into
> the database.
>
> Best regards,
>
> Jonathan Gennick
> mailto:jonathan_at_gennick.com * 906.387.1698
> http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org
>
> Monday, July 30, 2001, 6:30:51 PM, you wrote:
> DW> Thanks for the tips on the 'when' clause. I guess my dilemma is that my
> DW> "FLAG_TEXT" is not the only part of the field I want to filter on. For
> DW> example, let's say I want to filter out all the transactions containing the
> DW> word UNIX.
>
> DW> aaa,bbb,UNIX45689-2,ccc,ddd
>
> DW> In this case, I cannot use the 'when' clause like:
>
> DW> when (field <> 'UNIX')
>
> DW> I would need something equivalent to the SQL convention of "not like 'UNIX%'
> DW> " with the % wildcard. It appears that the 'when' clause is very limited in
> DW> this respect. Any suggestions on filtering out part of the text string?
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Gennick
> INET: jonathan_at_gennick.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
Scott Canaan (srcdco_at_rit.edu)
(716) 475-7886
"Life is like a sewer, what you get out of it depends on what you put into it" - Tom
Lehrer


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Scott Canaan
  INET: srcdco_at_ritvax.isc.rit.edu

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).