4 Replies Latest reply: Oct 15, 2013 7:40 AM by a5da4b45-4b57-4898-abf2-2032542c26be RSS

    SQL Loader When Condition

    sharpe
      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
          when (01:07) != '9000001' and (01:07) != '9000002' and (01:07) != '9000003' and (01:07) != '9000004'
          • 2. Re: SQL Loader When Condition
            sharpe
            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
              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

                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 .