Hi,

I am loading a file into multiple tables using SQL Loader:

(a) Format of the File is as follows:

H|820020080422|03/27/2008|90.00|90.00
D|99.609|ADLT|1|62.00

So each record starts with 'H' or 'D'

(b) I have written a Ctrl file which will insert into the oracle tables depending on the first character of the line
i.e. If first character is 'H' - then insert into table1
if first character is 'D' -then insert into table 2

My Code is
------
LOAD DATA
INFILE '$XXGVI_TOP/data/Test.trn'
APPEND
INTO TABLE GVIEO_DETAILS
WHEN (1:1) = 'D'
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( Record_Type "TRIM(:RECORD_TYPE)"
,Pax_Type "TRIM(:PAX_TYPE)"
,Pax_Count "TRIM(:PAX_COUNT)"
,Disc_Flag "TRIM(:DISC_FLAG)"
,Refund_Amt TERMINATED BY WHITESPACE "TRIM(:REFUND_AMT)"
,Optional_Name CONSTANT 'NNNN'
,Vendor_Number CONSTANT 'XXXXX'
)
INTO TABLE GVIEO_HEADER
WHEN (1:1) = 'H'
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
Record_Type "TRIM(:RECORD_TYPE)"
,Tour_Code "TRIM(:TOUR_CODE)"
,Sale_Date DATE(10) "MM/DD/YYYY"
,Refund_Total "TRIM(:REFUND_TOTAL)"
,Purchase_Total "TRIM(:PURCHASE_TOTAL)"
,Process_Flag CONSTANT 'N'
,File_Name CONSTANT 'Test.trn'
)

I am inserting some constant values also in the table.

When i am running this - It is inserting the record for 'D' type in GVIEO_DETAILS table.
But for 'H' type record - In GVIEO_HEADER table , it is inserting a Blank record i.e. All fields are NULL except for the constant column values i.e. last two columns are having the values but all other fields are NULL

Thanks

--Abhay