This discussion is archived
4 Replies Latest reply: Oct 15, 2013 5:40 AM by a5da4b45-4b57-4898-abf2-2032542c26be RSS

SQL Loader When Condition

sharpe Newbie
Currently Being Moderated
Hi. I'm currently using the When option in my SQL Loader script:
when (01:07) <> '9000001'
But now I'm finding I need to exclude multiple strings '9000001', '9000002','9000003','9000004'. Is there a way to do this? Any help would be greatly appreciated.
  • 1. Re: SQL Loader When Condition
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    when (01:07) != '9000001' and (01:07) != '9000002' and (01:07) != '9000003' and (01:07) != '9000004'
  • 2. Re: SQL Loader When Condition
    sharpe Newbie
    Currently Being Moderated
    Awesome. Thanks. One last question...if I have these strings (e.g. '9000001', etc...) stored in a different table is there anyway to reject records where this value is in my table? Something like a nested select statement?
    when (01:07) not in(select mystr from mytable)
    Let me know is referencing a table is possible or not. Thanks.
  • 3. Re: SQL Loader When Condition
    Barbara Boehmer Oracle ACE
    Currently Being Moderated
    sharpe wrote:
    Awesome. Thanks. One last question...if I have these strings (e.g. '9000001', etc...) stored in a different table is there anyway to reject records where this value is in my table? Something like a nested select statement?
    when (01:07) not in(select mystr from mytable)
    Let me know is referencing a table is possible or not. Thanks.
    As far as I know, you can't do that. I believe the operator has to be either = or != and the value to compare has to be a literal, not a select or function or expression. You could use either SQL*Loader (or an external table if your data is on the server not the client) to load the data into a staging table, then use SQL to insert ... where ... not in(select mystr from mytable).
  • 4. Re: SQL Loader When Condition
    a5da4b45-4b57-4898-abf2-2032542c26be Newbie
    Currently Being Moderated

    Hi , I am in need of a similar approach to reject Inserts on a table, Please let me know if u found a solution for the same .

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points