RE: SQL*Loader question- Transforming Data

From: Kevin Lange <kgel_at_ppoone.com>
Date: Mon, 30 Jul 2001 15:44:19 -0700
Message-ID: <F001.003597A7.20010730152601@fatcity.com>

You could consider doing someting like an awk script against your data looking for UNIX in that field. Taking this data and piping it to a new file to be loaded.

-----Original Message-----

Sent: Monday, July 30, 2001 5:31 PM
To: Multiple recipients of list ORACLE-L

Thanks for the tips on the 'when' clause. I guess my dilemma is that my "FLAG_TEXT" is not the only part of the field I want to filter on. For example, let's say I want to filter out all the transactions containing the word UNIX.

aaa,bbb,UNIX45689-2,ccc,ddd

In this case, I cannot use the 'when' clause like:

when (field <> 'UNIX')

I would need something equivalent to the SQL convention of "not like 'UNIX%' " with the % wildcard. It appears that the 'when' clause is very limited in this respect. Any suggestions on filtering out part of the text string?

TIA, David B. Wagoner
Database Administrator
Arsenal Digital Solutions Worldwide Inc. 4815 Emperor Blvd., Suite 110
Durham, NC 27703
Tel. (919) 941-4645
Fax (919) 474-0695
Email mailto:dwagoner_at_arsenaldigital.com Web http://www.arsenaldigital.com/

NOTICE *** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify the sender at (919) 941-4645 and delete this e-mail message from your computer. Thank you.

-----Original Message-----

Sent: Monday, July 30, 2001 4:15 PM
To: Multiple recipients of list ORACLE-L

Hello David,

You need to use the WHEN clause. Look at the example in the middle of page 147. If an input record doesn't match the conditions you specify following WHEN, it simply isn't loaded.

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, 2:56:22 PM, you wrote: DW> I'm using SQL*Loader to populate a database table with transaction records.

DW> The data file contains some transactions that I want to exclude from the
DW> load and they all contain the same text string, something like
DW> "xxx,yyy,FLAG_TEXT,xxx,yyy".  Can I use a SQL statement in the

SQL*Loader
DW> control file to filter out those transactions? Something that functions DW> like this pseudocode:

DW> if field like '%FLAG_TEXT%' then null; DW> else load;

DW> I've been reading the new Oracle SQL*Loader book by Gennick and Mishra which
DW> is a good book, but I'm still uncertain about this.

DW> Thanks,

DW> David B. Wagoner
DW> Database Administrator
DW> Arsenal Digital Solutions Worldwide Inc.
DW> 4815 Emperor Blvd., Suite 110
DW> Durham, NC 27703
DW> Tel. (919) 941-4645
DW> Fax (919) 474-0695
DW> Email  <mailto:dwagoner_at_arsenaldigital.com>
DW> mailto:dwagoner_at_arsenaldigital.com
DW> Web  <http://www.arsenaldigital.com/> http://www.arsenaldigital.com/


DW> ***  NOTICE  ***
DW> This e-mail message is confidential, intended only for the named
DW> recipient(s) above and may contain information that is privileged, work

DW> product or exempt from disclosure under applicable law. If you have DW> received this message in error, or are not the named recipient(s), please
DW> immediately notify the sender at (919) 941-4645 and delete this e-mail DW> message from your computer. Thank you.

--

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: David Wagoner
INET: dwagoner_at_arsenaldigital.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

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