SQL Loader - WHEN Clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: SQL Loader - WHEN Clause

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    SQL Loader - WHEN Clause

    I have to validate 2 columns while loading my data.

    I tried using this logic.

    Need to load RECORD_TYPE in (1, 3).

    Send me the syntax for the same.

    I tried something like this...
    LOAD DATA
    APPEND
    INTO TABLE IPDR_3G_REL2_TMP
    WHEN VENDOR != 'TRA' AND VENDOR != 'HEA'
    AND (RECORD_TYPE = '1' OR RECORD_TYPE = '3')
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS

    I am getting syntax error.

    Please revert.

    Cheers...
    Cheers!
    OraKid.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by balajiyes
    I tried something like this...
    LOAD DATA
    APPEND
    INTO TABLE IPDR_3G_REL2_TMP
    WHEN VENDOR != 'TRA' AND VENDOR != 'HEA'
    AND (RECORD_TYPE = '1' OR RECORD_TYPE = '3')
    FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS

    I am getting syntax error.
    The problem is OR operator between the two field conditions in the WHEN clause.

    Unfortunately SQL*Loader support only AND operator between different expressions in the WHEN clause.

    Maybe it will be easier for you to create a simple check constraint on the table before starting your load.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thank You jmodic.

    Let me think over.

    Just share how to handle this. thru SQL Loader.
    Cheers!
    OraKid.

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Will this work:
    AND (RECORD_TYPE IN ( '1', '3'))

    Tamil

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by tamilselvan
    Will this work:
    AND (RECORD_TYPE IN ( '1', '3'))
    No, it will not. You can't use IN operator, only equality operators ('=', '!=' or '<>') are permited in the WHEN clause expressions.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks guys.

    For the time being I am using RECORD_TYPE <> 2.

    But we will face this issue from Feb'06. coz we are goin to have 2 more types coming in.

    Before that we need to find a solution or we need to change our design.
    Cheers!
    OraKid.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    And what is stopping you from creating a simple CHECK constraint on that table, where you can easily check that only RECORD TYPE IN ('1', '3') are allowed?

    That way, your loader session will try to load records with any record_type, but all that violate that check criteria will be rejected by the constraint. So instead of letting SQL*Loader to do the checking you let the database to do it instead...
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Thanks jmodic.

    I will be loading around 3500 file per day in my temp table. each file having 40,000 records.

    Around mid night I will swap the partition.

    Having a CHECK constraint, will it impact my temp table. If yes, how much?
    Cheers!
    OraKid.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by balajiyes
    Having a CHECK constraint, will it impact my temp table. If yes, how much?
    Will it impact performance? Certanly.
    How severely? Hard to say, but I would speculate that it will be barely noticable.

    Instead of guessing, why don't you simply try it? Get a sample of your data and load it - once with a constraint in place and once without it.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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