2 Replies Latest reply: Nov 23, 2012 4:56 AM by 941425 RSS

    SQL Loader to append data in same table but using differnet WHEN clauses

    941425
      In my data file i have a header record and a detail record identified by Record_type = 1 and 2 respectively.
      The Database table has all the columns to capture detail records but i want to capture jus one column of header record now also in my existing table. So i have added that column (DATA_DATE)in my table but how to capture that value ?
      im writing my control file using two WHEN clauses, something like -

      load data
      into table t_bdn
      append
      when RECORD_TYPE = '2'
      FIELDS TERMINATED BY "|" TRAILING NULLCOLS
      (
      SEQUENCE_NO
      , RECORD_TYPE
      , DISTRIBUTOR_CODE
      , SUPPLIER_CODE
      , SUPPLIER_DISTRIBUTOR_CODE
      , DISTRIBUTOR_SKU
      , SUPPLIER_SKU
      )

      when RECORD_TYPE = '1'
      FIELDS TERMINATED BY "|" TRAILING NULLCOLS
      (
      SEQUENCE_NO FILLER
      , RECORD_TYPE FILLER
      , CREATE_DATE FILLER
      , DATA_DATE "NVL(to_date(:DATA_DATE, 'YYYY/MM/DD'),to_date('9999/12/31', 'YYYY/MM/DD'))"
      )


      im getting error " expecting INTO and foung WHEN RECORD_TYPE = '1' "

      if i give iNTO second time it will append a new row altogether in my table but i want the same row to be updated with this DATA_DATE value coming from RECORD_TYPE =1 and header record has 4 delimited data text fields only and i am interested in fetching just the 4th column..
      KIndly suggest what to do ?
        • 1. Re: SQL Loader to append data in same table but using differnet WHEN clauses
          Mark D Powell
          Ravneek, I could be wrong but sqlldr is a 'load' program, that is, it inserts data. I am unaware of any ability to update existing rows as you seem to want. What you appear to want to do is more the job of a merge statement.

          I would look at writing a pro* language, a .net, or a java program to perform inserts where some or all of the newly inserted rows are also to be updated.

          From the manual: (Oracle® Database Utilities 10g Release 2 (10.2) Part Number B14215-01)
          Updating Existing Rows

          The REPLACE method is a table replacement, not a replacement of individual rows. SQL*Loader does not update existing records, even if they have null columns. To update existing rows, use the following procedure:

          1. Load your data into a work table.

          2. Use the SQL language UPDATE statement with correlated subqueries.

          3. Drop the work table.


          HTH -- Mark D Powell --
          • 2. Re: SQL Loader to append data in same table but using differnet WHEN clauses
            941425
            Hi,

            Thanks for your response. And There seems to be no way out to append in same table using two different when clause, according to my requiremnet

            So, i have created a new temp table and m doing the second append into that table using the oher WHEN clause. And then In my procedure at back end, im updating that column value from this table. using plsql techniue.