Re: SQL*Loader When Stmt

I had a similar problem and am using multiple 'When' clauses, one for each record type. If your record type definitions are held in a table it's reasonable straight forward to automate the build of the control file and though the control file is large, it appears to have little effect on performance.

Ian

In article <7o7epi$v10$1_at_nnrp1.deja.com>,   teybrady_at_my-deja.com wrote:
>
>
> Does anyone know how to add a when clause that checks for multiple
> values instead of just one? I'm trying to load only certain records
> from a large data file into a table. Unfortunately I have 25
different
> types that I want to load instead of 1. The "or" method below isn't
> valid although "and" is valid between different columns.
>
> LOAD DATA
> INFILE 'g:\essbase\infiles\t&l\ntl2100a990730.txt'
> INTO TABLE TNL_DATA
> WHEN (TYPE = '05' or TYPE = '06' or TYPE = '07' or TYPE = '08' or
> TYPE = '09' or TYPE = '10' or TYPE = '11' or TYPE = '12' or
> TYPE = '15' or TYPE = '16' or TYPE = '18' or TYPE = '22' or
> TYPE = '24' or TYPE = '30' or TYPE = '31' or TYPE = '32' or
> TYPE = '33' or TYPE = '34' or TYPE = '35' or TYPE = '36' or
> TYPE = '37' or TYPE = '38' or TYPE = '39' or TYPE = '40' or
> TYPE = '44')
> FIELDS TERMINATED BY " "
> (WORK_DATE DATE "MM/DD/YY", WEEK, MONTH, YEAR, EMPLID, TYPE, SHIFT,
> CLASS, DEPT, P_DEPT, WBS, HOURS)
>
> I'm not to excited about making 25 passes through the data like this
> valid control file.
>
> LOAD DATA
> INFILE 'g:\essbase\infiles\t&l\ntl2100a990730.txt'
> INTO TABLE TNL_DATA
> WHEN (TYPE = '05')
> FIELDS TERMINATED BY " "
> (WORK_DATE DATE "MM/DD/YY", WEEK, MONTH, YEAR, EMPLID, TYPE, SHIFT,
> CLASS, DEPT, P_DEPT, WBS, HOURS)
>
> Is there some other way to do this? I'm not to familiar with
> SQL*Loader and can't figure this out using the CD Documentation.
>
> I have tried using "in" too and it is not allowed in the SQL*Loader.
> i.e. WHEN TYPE in ('05','06'...etc.
>
> Alternate solutions to the "when" statement are welcomed.
>
> Tey Brady
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 05 1999 - 12:29:15 CEST