WHEN clause in sqlloader ( cannot use IN?)

[Cookies]

I would like to load my flat file but only for data that has:
'4000','4001','4002','4003','4004','4005','4006','4007'

My below file works great for loading just the '4000', but I cannot figure out how to include more variables.

I tried the IN clause, I tried the OR clause, I tried the AND clause ...

OPTIONS
( SKIP = 5,
ERRORS = 1000,
ROWS = 128,
DIRECT = TRUE,
PARALLEL = FALSE
)

LOAD DATA
INFILE 'rolling-5min.csv'
BADFILE 'load_pricing.bad'
DISCARDFILE 'load_pricing.dis'
DISCARDMAX 10000000

APPEND
INTO TABLE T_PRICING
WHEN MRKT_LOCTN_ID = '4000'
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
filler1 filler,
ENERGY_MKT_CD constant 'NE',
MRKT_LOCTN_ID CHAR,
MKT_PRICE_DT date 'HH24:MI:SS',
MKT_PRCNG_TYPE_CD constant '5M',
ENERGY_NBR CHAR,
CNGSTN_NBR CHAR,
MRGNL_LS_NBR CHAR,
LMP_NBR CHAR
)

[slimdave]

Not on 9i are you? You could configure the file as an external table and get the IN () clause that way.

[TomazZ]

WHEN (MRKT_LOCTN_ID = '4000') AND (MRKT_LOCTN_ID = '4001') AND...

[jmodic]

Originally posted by TomazZ
WHEN (MRKT_LOCTN_ID = '4000') AND (MRKT_LOCTN_ID = '4001') AND...

I bet the result of the above expression will always be FALSE, won't it? So no rows will be loaded....

[TomazZ]

Originally posted by jmodic
I bet the result of the above expression will always be FALSE, won't it? So no rows will be loaded....

You won the bet!
I guess I am underslept today...

[Cookies]

Originally posted by jmodic
I bet the result of the above expression will always be FALSE, won't it? So no rows will be loaded....

Correct!

I tried that and It did not work.

Yes, I am on 9i, but new to 9i features.

[jovery]

As Slimdave said if your using 9i use External tables

Take a look at this for the concept

http://www.dbasupport.com/forums/sho...threadid=37299

and this for an example

http://www.oracle-base.com/Articles/...alTables9i.asp

HTH

[abhaysk]

Cant you just load to a Temp_Table & get the required records in Main_table, then blow up ur temp_table?

I feel this pretty easy.

[TomazZ]

You can just repeat this code in control file for all mrkt_loctn_id:

INTO TABLE T_PRICING
WHEN MRKT_LOCTN_ID = '4000'
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
filler1 filler,
ENERGY_MKT_CD constant 'NE',
MRKT_LOCTN_ID CHAR,
MKT_PRICE_DT date 'HH24:MI:SS',
MKT_PRCNG_TYPE_CD constant '5M',
ENERGY_NBR CHAR,
CNGSTN_NBR CHAR,
MRGNL_LS_NBR CHAR,
LMP_NBR CHAR
)

[abhaysk]

Originally posted by TomazZ
You can just repeat this code in control file for all mrkt_loctn_id:

Wouldnt be a good idea if one has too many "mrkt_loctn_id" unlike in this case having just 8 diff Ids