Home » RDBMS Server » Server Utilities » Problem SQL Loading a delimited file into two different tables? Please Help!
icon5.gif  Problem SQL Loading a delimited file into two different tables? Please Help! [message #244548] Wed, 13 June 2007 05:07 Go to next message
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

Re: Problem SQL Loading a delimited file into two different tables? Please Help! [message #244566 is a reply to message #244548] Wed, 13 June 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome on the forum.

Please always post your Oracle version (4 decimals).
Please read and follow How to format your posts.

Post your data, how could we answer your question without them?

Regards
Michel
icon5.gif  Re: Problem SQL Loading a delimited file into two different tables? Please Help! [message #244585 is a reply to message #244548] Wed, 13 June 2007 07:52 Go to previous messageGo to next message
stanjacko50
Messages: 6
Registered: June 2007
Location: London
Junior Member
Our Database version is 9.2.0.6

The data I'm trying to load is attached
Re: Problem SQL Loading a delimited file into two different tables? Please Help! [message #244617 is a reply to message #244585] Wed, 13 June 2007 09:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try to change CHAR for rec_type for second table to POSITION(1).

Regards
Michel
Re: Problem SQL Loading a delimited file into two different tables? Please Help! [message #244625 is a reply to message #244548] Wed, 13 June 2007 09:33 Go to previous message
stanjacko50
Messages: 6
Registered: June 2007
Location: London
Junior Member
Spot on - I knew it was something simple!

Thankyou very much Michel Cool
Previous Topic: How to populate one column based on another columns data?
Next Topic: received ora-30036
Goto Forum:
  


Current Time: Thu Dec 08 10:14:28 CST 2016

Total time taken to generate the page: 0.10897 seconds