OraFAQ Forum: Server Utilities » Data file not loaded into proper sequence.

Home » RDBMS Server » Server Utilities » Data file not loaded into proper sequence.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data file not loaded into proper sequence. [message #342401]

Fri, 22 August 2008 04:48

vs185020
Messages: 1
Registered: August 2008
Location: Gurgaon

Junior Member

I appear to be having some issues with SQL*Loader and was hoping someone may be able to tell me what is wrong with my control file. I am loading data through my control file where i am using WHEN condition. every thing is loaded properly but not in a proper sequence.

I have made a simple example to demonstrate my issue:

CREATE TABLE ncr_po_dhl_interface_test
(shipment_header_id NUMBER ,
creation_date DATE,
created_by NUMBER ,
last_update_login NUMBER,
last_update_date DATE ,
last_updated_by number,
HEADER_INFO VARCHAR2(10),
shipment_num VARCHAR2(50),
shipment_line_num VARCHAR2(10),
item_id NUMBER,
Product_name VARCHAR2(50),
quantity_shipped NUMBER,
serial_number VARCHAR2(50),
weight NUMBER,
ship_to_organization_id NUMBER,
expected_receipt_date DATE,
shipped_date DATE,
transaction_date DATE,
total_quantity_shipped NUMBER,
request_id NUMBER,
program_application_id NUMBER,
program_id NUMBER,
program_update_date DATE,
error_message VARCHAR2(1000),
status VARCHAR2(15)
)

My Control is as follows:

LOAD DATA
APPEND
INTO TABLE NCR_PO_DHL_INTERFACE_TEST
WHEN (HEADER_INFO='HD')
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(HEADER_INFO
,SHIPMENT_NUM
)
INTO TABLE NCR_PO_DHL_INTERFACE_TEST
WHEN (HEADER_INFO='DT')
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(HEADER_INFO POSITION (1)
,SHIPMENT_LINE_NUM
,ITEM_ID
,PRODUCT_NAME
,EXPECTED_RECEIPT_DATE SYSDATE
,SHIPPED_DATE SYSDATE
,TRANSACTION_DATE SYSDATE
,CREATION_DATE SYSDATE
,CREATED_BY CONSTANT '-1'
,LAST_UPDATE_LOGIN CONSTANT '-1'
,LAST_UPDATE_DATE SYSDATE
,LAST_UPDATED_BY CONSTANT '-1'
,REQUEST_ID CONSTANT '-1'
,PROGRAM_APPLICATION_ID CONSTANT '-1'
,PROGRAM_ID CONSTANT '-1'
,PROGRAM_UPDATE_DATE SYSDATE
,STATUS CONSTANT 'NEW'
)

The contents of my datafile is as follows:

HD,6080000027-12
DT,1,566279,4054-0553-7494
DT,2,566279,4054-0553-7494
DT,3,566279,4054-0553-7494
DT,4,566279,4054-0553-7494
HD,6080000027-13
DT,1,566279,4054-0553-7494
DT,2,566279,4054-0553-7494
DT,3,566279,4054-0553-7494
DT,4,566279,4054-0553-7494
HD,6080000027-14
DT,1,566279,4054-0553-7494
DT,2,566279,4054-0553-7494
DT,3,566279,4054-0553-7494
DT,4,566279,4054-0553-7494

After loading the data into table i am expexcting below result .

HEADER_INFO SHIPMENT_NUM SHIPMENT_LINE_NUM ITEM_ID PRODUCT_NAME
HD 6080000027-12
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494
HD 6080000027-13
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494
HD 6080000027-14
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494

But the expected resuld coming in the table are as below.

HD 6080000027-12
HD 6080000027-13
HD 6080000027-14
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494
DT 1 566279 4054-0553-7494
DT 2 566279 4054-0553-7494
DT 3 566279 4054-0553-7494
DT 4 566279 4054-0553-7494

due to this reason i cannot able to link the shipment (HD) and shipment line (DT) information to eacr other.

I really appericiate any one can give the the resolution.

Thanks
Vinay.

Report message to a moderator

Re: Data file not loaded into proper sequence. [message #342414 is a reply to message #342401]

Fri, 22 August 2008 05:01

Michel Cadot
Messages: 68650
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ Read OraFAQ Forum Guide
2/ Don't rate your own topic before any answer, we already know that you are proud of your questions.

Regards
Michel

[Updated on: Fri, 22 August 2008 05:01]

Report message to a moderator

Re: Data file not loaded into proper sequence. [message #342509 is a reply to message #342401]

Fri, 22 August 2008 09:55

Barbara Boehmer
Messages: 9092
Registered: November 2002
Location: California, USA

Senior Member

Unless I am missing something, the results that you are getting and the results that you want are identical except for the order, which is irrelevant in a relational database. Is there some error in what you posted? Do you realize that rows are not stored in the order input and are only ordered when you select them using an "order by" clause? In order to link something you must have a common field to link them with and I don't see that in your data.

Report message to a moderator

Previous Topic:	error : maximum length excedeed
Next Topic:	Can we import to another instance on the same box ?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 12 11:32:21 CDT 2024