CONDITIONAL LOADING USING SQLLOADER--Urgent

[anuragmin]

Hi,
How do I load records using sql loader using a specified criteria.

I have a table test with a region column. I want to load only those records which belong to certain regions and discard the rest of them.

For example I want to load records belonging to regions
'REGROLL' and 'WWF'.

I created a control file as followed

oad data
infile 'test.txt'
badfile 'ofasweb.bad'
discardfile 'ofasweb.dcs'
truncate
into table test when (REGION IN ('REGROLL','WWF'))
(company position(01:10) char,
time position(11:20) char,........................................
.................................................

But it gives a syntax error.
C:\ofus>sqlldr parfile=parfile.par

SQL*Loader: Release 8.1.7.0.0 - Production on Tue Jun 12 11:50:43 2001

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 6.
Expecting = or "<>", found "IN".
into table test when (REGION IN ('REGROLL','WWF'))



But if I create the control file as
load data
infile 'test.txt'
badfile 'ofasweb.bad'
discardfile 'ofasweb.dcs'
truncate
into table test when REGION = 'REGROLL'.............................
...............................

it takes it. Is there any way i can specify multiple values for the same column to filter by.

Thanks
Anurag

[tomate]

test following
into table test when REGION = 'REGROLL' and REGION ='WWF'

i hope it runs.

The IN command is relativly new command so it can happens that it isn't in the sql loader enviorment.

I hope i help you

Regards
Thomas

[anuragmin]

Originally posted by tomate
test following
into table test when REGION = 'REGROLL' and REGION ='WWF'

I tried that also but it discards teh records

The following is logged into the log file

Record 1: Discarded - failed all WHEN clauses.
Record 2: Discarded - failed all WHEN clauses.
Record 3: Discarded - failed all WHEN clauses.
Record 4: Discarded - failed all WHEN clauses.
Record 5: Discarded - failed all WHEN clauses.
Record 6: Discarded - failed all WHEN clauses.
Record 7: Discarded - failed all WHEN clauses.
Record 8: Discarded - failed all WHEN clauses.
......................................
.........................

Here is the sample of the records

MBOC YR_1991 REGROLL BSPSIEB 1.000000 1.000000
ALLEP YR_1991 REGROLL COFYE 24.000000 24.000000
ALLEP YR_1991 REGROLL COLOP 48.000000 48.000000
ALLEP YR_1991 REGROLL COEPAM 8.000000 8.000000

Please suggest a way I can do this with or without sql loader.

Thanks
Anurag

[anuragmin]

[i]Please suggest a way I can do this with or without sql loader or using someother utility if not possible in sql loader.


Thanks
Anurag

[sambavan]

Originally posted by tomate
test following
into table test when REGION = 'REGROLL' and REGION ='WWF'


Regards
Thomas

One correction, change that to

into table test when REGION = 'REGROLL' OR REGION ='WWF'


This would help.

Sam

[jmodic]

Unfortunately SQL*LOader doesn't allow OR operator in the WHEN clause. Multiple conditions can only be applied with AND operator (sigh).

What you can do is to use multiple INTO TABLE clauses in a single controlfile to mimic the behavior of OR conditions, like:

load data
infile 'test.txt'
badfile 'ofasweb.bad'
discardfile 'ofasweb.dcs'
truncate
into table test when REGION = 'REGROLL'
(company position(01:10) char,
time position(11:20) char,........................................)
into table test when REGION = 'WWF'
(company position(01:10) char,
time position(11:20) char,........................................)

[pareshg]

HI Juich,

What truncate command will do?

Paresh

[anuragmin]

Jmodic,
Thanks for Help. I will try it today.

Pareshg the truncate command will truncate the table before inserting the records from the sql loader.

Anurag

[anuragmin]

Hi Jurij,

Thanks a lot. It worked.
But since I have a lots and lots of region the sql loader control file has become too large.

Can I do this in shell script or perl program. If yes can somebody please post a sample program which i can change to use it for loading.

I will post the same question in a seperate thread also.

Please Help.

Thanks
Anurag