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

From: Soazig Forterre <soazig.forterre_at_laposte.net>
Date: 3 Apr 2002 09:42:25 -0800
Message-ID: <8fa56fe5.0204030942.5192c966_at_posting.google.com>

Hello,
I try without the (
see the control file below. but then I get the following message .

SQL*Loader-350: Syntax error at line 7.
Expecting "(", found keyword when.
WHEN LOCATION_COUNTRY_CODE IN ( 'FR' ,'DK','IS') I think, we are near enough from the solution, but not quite at it. [Quoted] Thank you for any help you can give me on this problem.

OPTIONS (skip = 2)
load data
infile '$HOME/rit/mind.in'
truncate
into table IMPORT_MIND
fields terminated by ' '
WHEN LOCATION_COUNTRY_CODE IN ( 'FR' ,'DK','IS') (
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 DATE(20) "YYYYMMDD HH24:MI:SS",

LAST_UPDATE_DATE DATE(20) "YYYYMMDD HH24:MI:SS", RECORD_STATUS
)

TurkBear <jgreco1_at_mn.rr.com> wrote in message news:<p6pjau88no3hmak3m1aqdfamt4afnsd2gt_at_4ax.com>...
> I believe so, but I haven't tried it for a long while so I may be remembering badly ( sometimes I am affected with CRAFT -
> Can't Remember A Freakin' Thing) ...
> ( My understanding is that almost any valid sql expression can be used)
>
>
> Bricklen <bricklen_at_shaw.ca> wrote:
>
> >You can use the IN keyword in the WHEN clause?! that's handy!!
> >
> >
> >TurkBear wrote:
> >>
> >> 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
> >[snipped]

regards
Soazig Received on Wed Apr 03 2002 - 19:42:25 CEST