Home » RDBMS Server » Server Utilities » Problem SQL Loading a delimited file into two different tables? Please Help!
 Problem SQL Loading a delimited file into two different tables? Please Help! [message #244548] |
Wed, 13 June 2007 05:07  |
stanjacko50
Messages: 6
Registered: June 2007
Location: London
|
Junior Member |
|
|
I have a file that contains HEADER records 1:1 = H, and LINE records 1:1 = L. Each line type contains different data. Header records have 14 fields and line records have only 7.
My load works, but only for the first APPEND, the second block does not work, and says 'Failed all WHEN clauses'. If I switch the INTO blocks round, whichever one is FIRST works, and the second one fails. I feel I am missing something quite small here, but can't work out what it is. Can anyone help?
In the log file for the first INTO block it sees the POSITION of the first field as 'FIRST', but in the second block it just see's the position of the first field as 'NEXT' - I think this is where my problem is. I have included the relevant part of my log file.
======================================================
LOAD DATA
INFILE *
APPEND
INTO TABLE LV_AP_ZZCLAIMS_HEADERS
WHEN REC_TYPE = 'H'
( REC_TYPE CHAR TERMINATED BY ","
,
ZZ_INVOICE_ID INTEGER EXTERNAL TERMINATED BY ","
,
INVOICE_NUM CHAR TERMINATED BY ","
,
INVOICE_DATE DATE "DD-MON-RRRR" TERMINATED BY ","
,
VENDOR_NUM CHAR TERMINATED BY ","
,
VENDOR_SITE_CODE CHAR TERMINATED BY ","
,
AMOUNT DECIMAL EXTERNAL TERMINATED BY ","
,
INVOICE_CURRENCY_CODE CHAR TERMINATED BY ","
,
DESCRIPTION CHAR TERMINATED BY ","
,
SOURCE CHAR TERMINATED BY ","
,
DOC_CATEGORY_CODE CHAR TERMINATED BY ","
,
PAYMENT_METHOD_LOOKUP_CODE CHAR TERMINATED BY ","
,
PAY_GROUP_LOOKUP_CODE CHAR TERMINATED BY ","
,
INVOICE_RECEIVED_DATE DATE "DD-MON-RRRR" TERMINATED BY ","
,
INTERFACE_STATUS CONSTANT "N"
)
INTO TABLE LV_AP_ZZCLAIMS_LINES
WHEN REC_TYPE = 'L'
( REC_TYPE CHAR TERMINATED BY ","
,
LINE_NUMBER INTEGER EXTERNAL TERMINATED BY ","
,
ZZ_INVOICE_ID INTEGER EXTERNAL TERMINATED BY ","
,
LINE_TYPE_LOOKUP_CODE CHAR TERMINATED BY ","
,
NET_AMOUNT DECIMAL EXTERNAL TERMINATED BY ","
,
DESCRIPTION CHAR TERMINATED BY ","
,
TAX_CODE CHAR TERMINATED BY ","
,
INTERFACE_STATUS CONSTANT "N"
)
========================================================
Log File: ATTACHED
[Updated on: Wed, 13 June 2007 05:26] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Jul 02 00:22:00 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
