Re: SQL*loader when clause for multiple values Correction to last post

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Tue, 02 Apr 2002 10:36:00 -0600
Message-ID: <56njause2n3flb2u4encjc9g64mvs66d7t_at_4ax.com>


[Quoted] Sorry , please remove the first parens after the when WHEN LOCATION_COUNTRY_CODE IN ( 'FR','DK','IS') ( LOCATION_ID,
etc...
RECORD_STATUS

 )                                                 NOTE End of first WHEN clause parens here 
Keep only 1 parens at the end

The code I was copying had positional references for each field so there were parens in the field description record_status(123:128)
and I didn't leave one of then out in my posting

TurkBear <jgreco1_at_mn.rr.com> wrote:

>
>Modify the position of the where clause and try this:
>
>fields terminated by ' '
>WHEN ( LOCATION_COUNTRY_CODE IN ( 'FR','DK','IS') - NOTE NO CLOSING PARENS HERE
>(
>LOCATION_ID,
>COMPANY_ID,
>COMPANY_FULL_NAME,
>COMPANY_SHORT_NAME "SUBSTR(:COMPANY_SHORT_NAME,1,30)",
>LOCATION_FULL_NAME,
>LOCATION_SHORT_NAME "SUBSTR(:LOCATION_SHORT_NAME,1,30)",
>LOCATION_ADDRESS1,
>LOCATION_ADDRESS2,
>LOCATION_ADDRESS3,
>LOCATION_POSTAL_CODE,
>LOCATION_TOWN,
>LOCATION_COUNTRY_CODE,
>LOCATION_COUNTRY_NAME,
>LOCATION_SO2_CODE,
>LOCATION_SO2_NAME,
>CHANNEL_SEGMENT_CODE,
>CHANNEL_SEGMENT_DESCRIPTION,
>CHANNEL_SUBSEGMENT_CODE,
>CHANNEL_SUBSEGMENT_DESCRIPTION,
>LOCATION_PHONE_NUMBER "decode(sign (length(:LOCATION_PHONE_NUMBER)-
>20),1,NULL, :LOCATION_PHONE_NUMBER)",
>LOCATION_PHONE_AREA_CODE,
>LOCATION_FAX_NUMBER "decode(sign( length(:LOCATION_FAX_NUMBER)-20),1,
>NULL, :LOCATION_FAX_NUMBER)" ,
>LOCATION_FAX_AREA_CODE ,
>LOCATION_EMAIL,
>HEADQUARTER_FLAG,
>LOCATION_URL,
>LOCATION_TAXID,
>CREATION_DATE,
>LAST_UPDATE_DATE,
>RECORD_STATUS
>) ) NOTE End of first WHERE clause parens here
>
>
>soazig.forterre_at_laposte.net (Soazig Forterre) wrote:
>
>>Hello,
>>I want to load a flat file using sql*loader only for a fiew value of a
>>field.
>>In sql it will be something like Where LOCATION_COUNTRY_CODE IN ('DK',
>>'FR', 'IS')
>>I try it in the when clause: WHEN (LOCATION_COUNTRY_CODE IN ('DK',
>>'FR', 'IS'))
>>but it doesn't work .
>>I also tried WHEN ((LOCATION_COUNTRY_CODE ='DK') OR
>>((LOCATION_COUNTRY_CODE ='FR')) but it doesn't work
>>I can restrict the load to one value by WHEN ( LOCATION_COUNTRY_CODE =
>>'FR' ) but I have not found yet, how to restrict it to several values.
>>Of course I can use 3 control files to load only 'DK', then only
>>'FR', then only 'IS', but in the future I will have 10 or 15 entries,
>>and I think that 15 control file to load only one file would be a
>>waste of time, and difficult to support.
>>My control file looks like this
>>
>>OPTIONS (skip = 2)
>>load data
>>infile '$HOME/rit/mind.in'
>>truncate
>>into table IMPORT_MIND
>>WHEN ( LOCATION_COUNTRY_CODE = 'FR' )
>>fields terminated by ' '
>>(
>>LOCATION_ID,
>>COMPANY_ID,
>>COMPANY_FULL_NAME,
>>COMPANY_SHORT_NAME "SUBSTR(:COMPANY_SHORT_NAME,1,30)",
>>LOCATION_FULL_NAME,
>>LOCATION_SHORT_NAME "SUBSTR(:LOCATION_SHORT_NAME,1,30)",
>>LOCATION_ADDRESS1,
>>LOCATION_ADDRESS2,
>>LOCATION_ADDRESS3,
>>LOCATION_POSTAL_CODE,
>>LOCATION_TOWN,
>>LOCATION_COUNTRY_CODE,
>>LOCATION_COUNTRY_NAME,
>>LOCATION_SO2_CODE,
>>LOCATION_SO2_NAME,
>>CHANNEL_SEGMENT_CODE,
>>CHANNEL_SEGMENT_DESCRIPTION,
>>CHANNEL_SUBSEGMENT_CODE,
>>CHANNEL_SUBSEGMENT_DESCRIPTION,
>>LOCATION_PHONE_NUMBER "decode(sign (length(:LOCATION_PHONE_NUMBER)-
>>20),1,NULL, :LOCATION_PHONE_NUMBER)",
>>LOCATION_PHONE_AREA_CODE,
>>LOCATION_FAX_NUMBER "decode(sign( length(:LOCATION_FAX_NUMBER)-20),1,
>>NULL, :LOCATION_FAX_NUMBER)" ,
>>LOCATION_FAX_AREA_CODE ,
>>LOCATION_EMAIL,
>>HEADQUARTER_FLAG,
>>LOCATION_URL,
>>LOCATION_TAXID,
>>CREATION_DATE,
>>LAST_UPDATE_DATE,
>>RECORD_STATUS
>>)
>>
>>
>>Any help will be welcome.
>>thanks a lot.
>>best regards Soazig Forterre
Received on Tue Apr 02 2002 - 18:36:00 CEST

Original text of this message