This discussion is archived
2 Replies Latest reply: Nov 23, 2012 2:56 AM by 941425 RSS

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

941425 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

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