Re: SQL*loader when clause for multiple values

From: TurkBear <jgreco1_at_mn.rr.com>
Date: Tue, 02 Apr 2002 10:25:54 -0600
Message-ID: <3imjauk3149kvn40s6mno3bh1c686esoh6_at_4ax.com>

[Quoted] 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:25:54 CEST