CONDITIONAL LOADING USING SQLLOADER--Urgent
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: CONDITIONAL LOADING USING SQLLOADER--Urgent

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    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.


  2. #2
    Join Date
    Mar 2001
    Posts
    188
    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

  3. #3
    Join Date
    Sep 2000
    Posts
    362
    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.


  4. #4
    Join Date
    Sep 2000
    Posts
    362
    [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.


  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  7. #7
    Join Date
    May 2001
    Location
    London
    Posts
    149
    HI Juich,

    What truncate command will do?

    Paresh

  8. #8
    Join Date
    Sep 2000
    Posts
    362
    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.


  9. #9
    Join Date
    Sep 2000
    Posts
    362
    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
  •  



Click Here to Expand Forum to Full Width