1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Issue with Conditional SQLLDR process

Discussion in 'SQL PL/SQL' started by y001778, Jan 30, 2012.

  1. y001778

    y001778 Guest

    Using sqlldr in 10g on Linux to load a large flat file / ach file layout into multiple tables for downstream translation processes (not part of my problem). The issue is ,my sqlldr task is working to the point that it is loading the data extremely fast however , the issue I'm running into is that the source data is broken down into multiple customer batches within the file and each batch's header and trailer record need to be inserted into a seperate table than the actual detail records. The batch header records start with a '5' and the record trailer information starts with a '8' which both record types need to be inserted into a table called batch_hdr_trl. The detail records that start with a '6' go into a "detal" table. Problem is when my sqlldr script executes it inserts all of the data to the correct tables , however it creates a 2 new records for every batch itteration, when that needs to be combined into one record. The batch header record fills in half of the fields in a record, and the batch trailer record fills in the remainder. what I am getting is half of the data on one record, the other on a new record. Is there a way to combine the conditional load to say "when you encounter '5' and '8' insert detail into one record ( sorry for bizzarre explanation) :

    My CTL File :

    UNRECOVERABLE

    LOAD DATA

    INFILE 'c:\temp\SAMPLE.DAT'

    INTO TABLE BATCH_HR_TR
    WHEN (1) = '5'
    (
    BH_ORIGINATION_ID POSITION(2:3),
    BH_BANK_SHORT_NAME POSITION(4:19),
    BH_BATCH_NUMBER POSITION(20:25),
    BH_MAP_ID POSITION(26:35),
    BH_RPS_ENTRY_CLASS POSITION(36:38),
    BH_RPS_ENTRY_DESC POSITION(39:48),
    )
    INTO TABLE BATCH_DETAIL
    WHEN (1) = '6'
    (
    SOURCE_ID POSITION(2:10),
    INSTITUTION_ID POSITION(12:21),
    STANDARD_PAYEE_NUM POSITION(22:30),
    REMIT_CENTER_NUM POSITION(31:39),
    COMPANY_ID POSITION(40:49),
    MERCHANT_NAME POSITION(50:65),
    MERCHANT_ADDRESS_1 POSITION(66:100),
    MERCHANT_ADDRESS_2 POSITION(101:135),
    MERCHANT_CITY POSITION(136:159),
    MERCHANT_STATE POSITION(160:161),
    MERCHANT_ZIP_5 POSITION(162:166),
    MERCHANT_ZIP_4 POSITION(167:170),
    MERCHANT_ZIP_2 POSITION(171:172),
    MERCHANT_RT_NUMBER POSITION(173:181),
    MERCHANT_DDA POSITION(182:199),
    )
    INTO TABLE BATCH_HR_TR
    WHEN (1) = '8'
    (
    BT_FILLER_1 POSITION(2:3),
    BT_CREDIT_COUNT POSITION(4:10),
    BT_CREDIT_AMOUNT POSITION(11:25),
    BT_DEBIT_COUNT POSITION(26:32),
    )

    Both the '5' and the '8' record conditions need to insert data into one record.

    sample file layout :

    5CFM3 FAKE HEADERS0000131111000043 5
    601020000 DETAIL 00.00
    601020000 DETAIL 00.00
    8CFM3 FAKE TRAILER 0003111043 TOTAL
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    CONTINUEIF could be an option. But I think it works only to consider the NEXT line as a part of current line.