DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: WHEN clause in sqlloader ( cannot use IN?)

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    WHEN clause in sqlloader ( cannot use IN?)

    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
    )
    - Cookies

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Not on 9i are you? You could configure the file as an external table and get the IN () clause that way.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Re: WHEN clause in sqlloader ( cannot use IN?)

    WHEN (MRKT_LOCTN_ID = '4000') AND (MRKT_LOCTN_ID = '4001') AND...
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Re: WHEN clause in sqlloader ( cannot use IN?)

    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422

    Re: Re: Re: WHEN clause in sqlloader ( cannot use IN?)

    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...
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  6. #6
    Join Date
    Oct 2002
    Posts
    182

    Re: Re: Re: WHEN clause in sqlloader ( cannot use IN?)

    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.
    - Cookies

  7. #7
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    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
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  8. #8
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  9. #9
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    You can just repeat this code in control file for all mrkt_loctn_id:
    Code:
    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
    )
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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