Re: SQL*loader when clause for multiple values

From: Bricklen <bricklen_at_shaw.ca>
Date: Tue, 02 Apr 2002 16:12:40 GMT
Message-ID: <3CA9D7A9.EAF704EB_at_shaw.ca>

From what I could find, you cannot use OR in the WHEN clause, only AND. Very limiting. You can get around this by using the same table specs for each table, with a different WHEN clause. Try something like the following.

OPTIONS (skip = 2)
load data
infile '$HOME/rit/mind.in'
truncate
into table IMPORT_MIND
WHEN LOCATION_COUNTRY_CODE = 'FR'
fields terminated by WHITESPACE (or you can keep the ' ' you had, up to you)
(

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
)
into table IMPORT_MIND
WHEN LOCATION_COUNTRY_CODE = 'DK' -- multiple table specs, one load fields terminated by WHITESPACE
(

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
)
into table IMPORT_MIND
WHEN LOCATION_COUNTRY_CODE = 'IS' -- multiple table specs, one load fields terminated by WHITESPACE
(

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
) Received on Tue Apr 02 2002 - 18:12:40 CEST