-
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
Appreciation is a wonderful thing;
It makes what is excellent in others belong to us as well.
-
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
-
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
Appreciation is a wonderful thing;
It makes what is excellent in others belong to us as well.
-
[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
Appreciation is a wonderful thing;
It makes what is excellent in others belong to us as well.
-
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
Thanx
Sam
Life is a journey, not a destination!
-
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,........................................)
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
HI Juich,
What truncate command will do?
Paresh
-
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
Appreciation is a wonderful thing;
It makes what is excellent in others belong to us as well.
-
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
Appreciation is a wonderful thing;
It makes what is excellent in others belong to us as well.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|