SQL*loader when clause for multiple values
From: Soazig Forterre <soazig.forterre_at_laposte.net>
Date: 2 Apr 2002 00:23:27 -0800
Message-ID: <8fa56fe5.0204020023.264d0994_at_posting.google.com>
Hello,
[Quoted] 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
LOCATION_SO2_CODE,
LOCATION_SO2_NAME,
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,
LAST_UPDATE_DATE,
RECORD_STATUS
)
Date: 2 Apr 2002 00:23:27 -0800
Message-ID: <8fa56fe5.0204020023.264d0994_at_posting.google.com>
Hello,
[Quoted] 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 - 10:23:27 CEST