Home » RDBMS Server » Server Utilities » How to associate a header record to its corresponding detail record (Oracle 10g, TOAD 10.5)
| How to associate a header record to its corresponding detail record [message #554709] |
Wed, 16 May 2012 12:14  |
concorde800
Messages: 52
Registered: May 2007
|
Member |
|
|
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data
into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
business_date POSITION(16:23) DATE "YYYYMMDD",
transaction_date POSITION(24:37) DATE "YYYYMMDDHH24MISS",
location POSITION(38:47) INTEGER EXTERNAL,
register_id POSITION(48:52) CHAR NULLIF register_id = '-1',
banner_id POSITION(53:56) CHAR NULLIF banner_id = '-1',
line_media_id POSITION(57:66) CHAR NULLIF line_media_id = '-1',
selling_item_id POSITION(67:91) CHAR NULLIF selling_item_id = '-1',
cust_order_header_id POSITION(92:121) CHAR NULLIF cust_order_header_id = '-1',
cust_order_line_id POSITION(122:151) CHAR NULLIF cust_order_line_id = '-1',
cust_order_create_date POSITION(152:159) DATE "YYYYMMDD",
cashier_id POSITION(160:169) CHAR NULLIF cashier_id = '-1',
salesperson_id POSITION(170:179) CHAR NULLIF salesperson_id = '-1',
cust_id_type POSITION(180:185) CHAR NULLIF cust_id_type = '-1',
cust_id_number POSITION(186:201) CHAR NULLIF cust_id_number = '-1',
tran_number POSITION(202:211) INTEGER EXTERNAL,
orig_reg_id POSITION(212:216) CHAR,
orig_tran_number POSITION(217:226) INTEGER EXTERNAL,
tran_header_number POSITION(227:246) INTEGER EXTERNAL,
revision_number POSITION(247:249) INTEGER EXTERNAL,
sales_sign POSITION(250:250) CHAR,
tran_type POSITION(251:256) CHAR,
sub_tran_type POSITION(257:262) CHAR NULLIF sub_tran_type = '-1',
retail_type POSITION(263:263) CHAR,
item_seq_no POSITION(264:267) INTEGER EXTERNAL,
employee_number POSITION(268:277) CHAR NULLIF employee_number = '-1',
receipt_ind POSITION(278:278) CHAR,
reason_code POSITION(279:284) CHAR NULLIF reason_code = '-1',
vendor_no POSITION(285:294) INTEGER EXTERNAL,
item_type POSITION(295:300) CHAR,
item POSITION(301:325) CHAR,
ref_item POSITION(326:350) CHAR,
taxable_ind POSITION(351:351) CHAR,
entry_mode POSITION(352:357) CHAR,
dept POSITION(358:361) INTEGER EXTERNAL,
class POSITION(362:365) INTEGER EXTERNAL,
subclass POSITION(366:369) INTEGER EXTERNAL,
total_sales_qty POSITION(370:381) INTEGER EXTERNAL ":total_sales_qty/10000",
total_tran_value POSITION(382:401) DECIMAL EXTERNAL ":total_tran_value/10000",
override_reason POSITION(402:407) CHAR NULLIF override_reason = '-1',
return_reason POSITION(408:413) CHAR NULLIF return_reason = '-1',
total_orig_sign POSITION(414:414) CHAR,
total_orig_sales_value POSITION(415:434) DECIMAL EXTERNAL ":total_orig_sales_value/10000",
weather POSITION(435:440) CHAR,
temperature POSITION(441:446) CHAR,
traffic POSITION(447:452) CHAR,
construction POSITION(453:458) CHAR,
drop_ship_ind POSITION(459:459) CHAR,
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_THEAD"
)
-- ==================================================================
INTO TABLE post1.tdetl
WHEN (1:5) = 'TDETL'
TRAILING NULLCOLS
(file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
discount_type POSITION(16:21) CHAR,
promo_tran_type POSITION(22:27) CHAR,
promotion_number POSITION(28:37) INTEGER EXTERNAL,
promo_component_number POSITION(38:47) INTEGER EXTERNAL,
coupon_number POSITION(48:63) CHAR,
coupon_ref_number POSITION(64:79) CHAR,
sales_qty POSITION(80:91) INTEGER EXTERNAL ":sales_qty/10000",
transaction_sign POSITION(92:92) CHAR,
transaction_value POSITION(93:112) DECIMAL EXTERNAL ":transaction_value/10000",
discount_value POSITION(113:132) DECIMAL EXTERNAL ":discount_value/10000",
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_TDETL"
)
A particular THEAD value may have 0, 1 or many TDETL corresponding values.
below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated ? Can we assign a unique value possibly?
|
|
|
|
| Re: How to associate a header record to its corresponding detail record [message #554710 is a reply to message #554709] |
Wed, 16 May 2012 12:21  |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
concorde800 wrote on Wed, 16 May 2012 13:14I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data
into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated ? Can we assign a unique value possibly?
One way I can think of would be to use a sequence. You could put the value into a dummy table for when it is the THEAD, then use a function to lookup the value when it is a TDETL.
That was my first though, but I am pretty sure there are better ways.
|
|
|
|
|
|
|
|
|
|
| Re: How to associate a header record to its corresponding detail record [message #554718 is a reply to message #554713] |
Wed, 16 May 2012 17:08 |
 |
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can add a column to each table to store a sequence that relates them. If you then use SEQUENCE(MAX) for each of those columns in your control file, then it will load the proper values into the columns, beginning with the maximum existing sequence in the table plus 1. You can then join those tables based on the sequence column for use in select statements and filter them as you wish. Please see the demonstration below.
-- test.dat:
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
-- test.ctl
LOAD DATA
APPEND
INTO TABLE thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(sqlldr_seq SEQUENCE(MAX),
file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
business_date POSITION(16:23) DATE "YYYYMMDD",
transaction_date POSITION(24:37) DATE "YYYYMMDDHH24MISS",
location POSITION(38:47) INTEGER EXTERNAL,
register_id POSITION(48:52) CHAR NULLIF register_id = '-1',
banner_id POSITION(53:56) CHAR NULLIF banner_id = '-1',
line_media_id POSITION(57:66) CHAR NULLIF line_media_id = '-1',
selling_item_id POSITION(67:91) CHAR NULLIF selling_item_id = '-1',
cust_order_header_id POSITION(92:121) CHAR NULLIF cust_order_header_id = '-1',
cust_order_line_id POSITION(122:151) CHAR NULLIF cust_order_line_id = '-1',
cust_order_create_date POSITION(152:159) DATE "YYYYMMDD",
cashier_id POSITION(160:169) CHAR NULLIF cashier_id = '-1',
salesperson_id POSITION(170:179) CHAR NULLIF salesperson_id = '-1',
cust_id_type POSITION(180:185) CHAR NULLIF cust_id_type = '-1',
cust_id_number POSITION(186:201) CHAR NULLIF cust_id_number = '-1',
tran_number POSITION(202:211) INTEGER EXTERNAL,
orig_reg_id POSITION(212:216) CHAR,
orig_tran_number POSITION(217:226) INTEGER EXTERNAL,
tran_header_number POSITION(227:246) INTEGER EXTERNAL,
revision_number POSITION(247:249) INTEGER EXTERNAL,
sales_sign POSITION(250:250) CHAR,
tran_type POSITION(251:256) CHAR,
sub_tran_type POSITION(257:262) CHAR NULLIF sub_tran_type = '-1',
retail_type POSITION(263:263) CHAR,
item_seq_no POSITION(264:267) INTEGER EXTERNAL,
employee_number POSITION(268:277) CHAR NULLIF employee_number = '-1',
receipt_ind POSITION(278:278) CHAR,
reason_code POSITION(279:284) CHAR NULLIF reason_code = '-1',
vendor_no POSITION(285:294) INTEGER EXTERNAL,
item_type POSITION(295:300) CHAR,
item POSITION(301:325) CHAR,
ref_item POSITION(326:350) CHAR,
taxable_ind POSITION(351:351) CHAR,
entry_mode POSITION(352:357) CHAR,
dept POSITION(358:361) INTEGER EXTERNAL,
class POSITION(362:365) INTEGER EXTERNAL,
subclass POSITION(366:369) INTEGER EXTERNAL,
total_sales_qty POSITION(370:381) INTEGER EXTERNAL ":total_sales_qty/10000",
total_tran_value POSITION(382:401) DECIMAL EXTERNAL ":total_tran_value/10000",
override_reason POSITION(402:407) CHAR NULLIF override_reason = '-1',
return_reason POSITION(408:413) CHAR NULLIF return_reason = '-1',
total_orig_sign POSITION(414:414) CHAR,
total_orig_sales_value POSITION(415:434) DECIMAL EXTERNAL ":total_orig_sales_value/10000",
weather POSITION(435:440) CHAR,
temperature POSITION(441:446) CHAR,
traffic POSITION(447:452) CHAR,
construction POSITION(453:458) CHAR,
drop_ship_ind POSITION(459:459) CHAR,
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_THEAD"
)
-- ==================================================================
INTO TABLE tdetl
WHEN (1:5) = 'TDETL'
TRAILING NULLCOLS
(sqlldr_seq SEQUENCE(MAX),
file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
discount_type POSITION(16:21) CHAR,
promo_tran_type POSITION(22:27) CHAR,
promotion_number POSITION(28:37) INTEGER EXTERNAL,
promo_component_number POSITION(38:47) INTEGER EXTERNAL,
coupon_number POSITION(48:63) CHAR,
coupon_ref_number POSITION(64:79) CHAR,
sales_qty POSITION(80:91) INTEGER EXTERNAL ":sales_qty/10000",
transaction_sign POSITION(92:92) CHAR,
transaction_value POSITION(93:112) DECIMAL EXTERNAL ":transaction_value/10000",
discount_value POSITION(113:132) DECIMAL EXTERNAL ":discount_value/10000",
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_TDETL"
)
-- ==================================================================
INTO TABLE ttail
WHEN (1:5) = 'TTAIL'
TRAILING NULLCOLS
(sqlldr_seq SEQUENCE(MAX),
position21 POSITION(21:21) CHAR)
-- tables:
SCOTT@orcl_11gR2> CREATE TABLE thead
2 (sqlldr_seq NUMBER,
3 file_type VARCHAR2(5),
4 file_line NUMBER,
5 business_date DATE,
6 transaction_date DATE,
7 location NUMBER,
8 register_id NUMBER,
9 banner_id NUMBER,
10 line_media_id NUMBER,
11 selling_item_id VARCHAR2(24),
12 cust_order_header_id NUMBER,
13 cust_order_line_id NUMBER,
14 cust_order_create_date DATE,
15 cashier_id NUMBER,
16 salesperson_id NUMBER,
17 cust_id_type NUMBER,
18 cust_id_number NUMBER,
19 tran_number NUMBER,
20 orig_reg_id NUMBER,
21 orig_tran_number NUMBER,
22 tran_header_number NUMBER,
23 revision_number NUMBER,
24 sales_sign NUMBER,
25 tran_type NUMBER,
26 sub_tran_type NUMBER,
27 retail_type VARCHAR2(1),
28 item_seq_no NUMBER,
29 employee_number NUMBER,
30 receipt_ind NUMBER,
31 reason_code NUMBER,
32 vendor_no NUMBER,
33 item_type VARCHAR2(6),
34 item VARCHAR2(25),
35 ref_item VARCHAR2(25),
36 taxable_ind VARCHAR2(1),
37 entry_mode VARCHAR2(6),
38 dept NUMBER,
39 class NUMBER,
40 subclass NUMBER,
41 total_sales_qty NUMBER,
42 total_tran_value NUMBER,
43 override_reason NUMBER,
44 return_reason NUMBER,
45 total_orig_sign VARCHAR2(1),
46 total_orig_sales_value NUMBER,
47 weather VARCHAR2(6),
48 temperature VARCHAR2(6),
49 traffic VARCHAR2(6),
50 construction VARCHAR2(6),
51 drop_ship_ind VARCHAR2(6),
52 create_date DATE,
53 create_id VARCHAR2(30),
54 create_proc VARCHAR2(10)
55 )
56 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE tdetl
2 (sqlldr_seq NUMBER,
3 file_type VARCHAR2(5),
4 file_line NUMBER,
5 discount_type VARCHAR2(6),
6 promo_tran_type VARCHAR2(6),
7 promotion_number NUMBER,
8 promo_component_number VARCHAR2(10),
9 coupon_number VARCHAR2(16),
10 coupon_ref_number VARCHAR2(16),
11 sales_qty NUMBER,
12 transaction_sign VARCHAR2(1),
13 transaction_value NUMBER,
14 discount_value NUMBER,
15 create_date DATE,
16 create_id VARCHAR2(30),
17 create_proc VARCHAR2(10)
18 )
19 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ttail
2 (sqlldr_seq NUMBER,
3 position21 NUMBER)
4 /
Table created.
-- load and results:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log DATA=test.dat
SCOTT@orcl_11gR2> SELECT * FROM thead
2 /
SQLLDR_SEQ FILE_ FILE_LINE BUSINESS_ TRANSACTI LOCATION REGISTER_ID
---------- ----- ---------- --------- --------- ---------- -----------
BANNER_ID LINE_MEDIA_ID SELLING_ITEM_ID CUST_ORDER_HEADER_ID
---------- ------------- ------------------------ --------------------
CUST_ORDER_LINE_ID CUST_ORDE CASHIER_ID SALESPERSON_ID CUST_ID_TYPE
------------------ --------- ---------- -------------- ------------
CUST_ID_NUMBER TRAN_NUMBER ORIG_REG_ID ORIG_TRAN_NUMBER TRAN_HEADER_NUMBER
-------------- ----------- ----------- ---------------- ------------------
REVISION_NUMBER SALES_SIGN TRAN_TYPE SUB_TRAN_TYPE R ITEM_SEQ_NO
--------------- ---------- ---------- ------------- - -----------
EMPLOYEE_NUMBER RECEIPT_IND REASON_CODE VENDOR_NO ITEM_T
--------------- ----------- ----------- ---------- ------
ITEM REF_ITEM T ENTRY_ DEPT
------------------------- ------------------------- - ------ ----------
CLASS SUBCLASS TOTAL_SALES_QTY TOTAL_TRAN_VALUE OVERRIDE_REASON
---------- ---------- --------------- ---------------- ---------------
RETURN_REASON T TOTAL_ORIG_SALES_VALUE WEATHE TEMPER TRAFFI CONSTR DROP_S
------------- - ---------------------- ------ ------ ------ ------ ------
CREATE_DA CREATE_ID CREATE_PRO
--------- ------------------------------ ----------
1 THEAD 2 14-SEP-11 14-SEP-11 209 1
1 0
16-MAY-12 SCOTT LOAD_THEAD
2 THEAD 12 14-SEP-11 14-SEP-11 209 1
1 0 -1 20110914-1
16-MAY-12 SCOTT LOAD_THEAD
3 THEAD 21 14-SEP-11 14-SEP-11 209 1
1 0
16-MAY-12 SCOTT LOAD_THEAD
3 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM tdetl
2 /
SQLLDR_SEQ FILE_ FILE_LINE DISCOU PROMO_ PROMOTION_NUMBER PROMO_COMP
---------- ----- ---------- ------ ------ ---------------- ----------
COUPON_NUMBER COUPON_REF_NUMBE SALES_QTY T TRANSACTION_VALUE DISCOUNT_VALUE
---------------- ---------------- ---------- - ----------------- --------------
CREATE_DA CREATE_ID CREATE_PRO
--------- ------------------------------ ----------
1 TDETL 13 CMPSPL 1004 0 000
000010000P000000 0000000019990000 0 2 0
16-MAY-12 SCOTT LOAD_TDETL
2 TDETL 22 EMPDSC 1005 0
0
16-MAY-12 SCOTT LOAD_TDETL
3 TDETL 23 SCOUP 1006 0
973452 0
16-MAY-12 SCOTT LOAD_TDETL
3 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM ttail
2 /
SQLLDR_SEQ POSITION21
---------- ----------
1 0
2 1
3 2
3 rows selected.
-- select joining tables on sequence column:
SCOTT@orcl_11gR2> SELECT h.sqlldr_seq head_seq, d.sqlldr_seq detl_seq, t.sqlldr_seq tail_seq, t.position21
2 FROM thead h, tdetl d, ttail t
3 WHERE h.sqlldr_seq = d.sqlldr_seq(+)
4 AND h.sqlldr_seq = t.sqlldr_seq(+)
5 ORDER BY head_seq
6 /
HEAD_SEQ DETL_SEQ TAIL_SEQ POSITION21
---------- ---------- ---------- ----------
1 1 1 0
2 2 2 1
3 3 3 2
3 rows selected.
|
|
|
|
|
|
| Re: How to associate a header record to its corresponding detail record [message #554720 is a reply to message #554719] |
Wed, 16 May 2012 17:25 |
|
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If you use a database sequence, you can use nextval and currval and rows=1 option to get matching sequences. I have provided a modified demonstration below.
-- test.dat
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
-- test.ctl
OPTIONS(ROWS=1)
LOAD DATA
APPEND
INTO TABLE thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(sqlldr_seq "test_seq.NEXTVAL",
file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
business_date POSITION(16:23) DATE "YYYYMMDD",
transaction_date POSITION(24:37) DATE "YYYYMMDDHH24MISS",
location POSITION(38:47) INTEGER EXTERNAL,
register_id POSITION(48:52) CHAR NULLIF register_id = '-1',
banner_id POSITION(53:56) CHAR NULLIF banner_id = '-1',
line_media_id POSITION(57:66) CHAR NULLIF line_media_id = '-1',
selling_item_id POSITION(67:91) CHAR NULLIF selling_item_id = '-1',
cust_order_header_id POSITION(92:121) CHAR NULLIF cust_order_header_id = '-1',
cust_order_line_id POSITION(122:151) CHAR NULLIF cust_order_line_id = '-1',
cust_order_create_date POSITION(152:159) DATE "YYYYMMDD",
cashier_id POSITION(160:169) CHAR NULLIF cashier_id = '-1',
salesperson_id POSITION(170:179) CHAR NULLIF salesperson_id = '-1',
cust_id_type POSITION(180:185) CHAR NULLIF cust_id_type = '-1',
cust_id_number POSITION(186:201) CHAR NULLIF cust_id_number = '-1',
tran_number POSITION(202:211) INTEGER EXTERNAL,
orig_reg_id POSITION(212:216) CHAR,
orig_tran_number POSITION(217:226) INTEGER EXTERNAL,
tran_header_number POSITION(227:246) INTEGER EXTERNAL,
revision_number POSITION(247:249) INTEGER EXTERNAL,
sales_sign POSITION(250:250) CHAR,
tran_type POSITION(251:256) CHAR,
sub_tran_type POSITION(257:262) CHAR NULLIF sub_tran_type = '-1',
retail_type POSITION(263:263) CHAR,
item_seq_no POSITION(264:267) INTEGER EXTERNAL,
employee_number POSITION(268:277) CHAR NULLIF employee_number = '-1',
receipt_ind POSITION(278:278) CHAR,
reason_code POSITION(279:284) CHAR NULLIF reason_code = '-1',
vendor_no POSITION(285:294) INTEGER EXTERNAL,
item_type POSITION(295:300) CHAR,
item POSITION(301:325) CHAR,
ref_item POSITION(326:350) CHAR,
taxable_ind POSITION(351:351) CHAR,
entry_mode POSITION(352:357) CHAR,
dept POSITION(358:361) INTEGER EXTERNAL,
class POSITION(362:365) INTEGER EXTERNAL,
subclass POSITION(366:369) INTEGER EXTERNAL,
total_sales_qty POSITION(370:381) INTEGER EXTERNAL ":total_sales_qty/10000",
total_tran_value POSITION(382:401) DECIMAL EXTERNAL ":total_tran_value/10000",
override_reason POSITION(402:407) CHAR NULLIF override_reason = '-1',
return_reason POSITION(408:413) CHAR NULLIF return_reason = '-1',
total_orig_sign POSITION(414:414) CHAR,
total_orig_sales_value POSITION(415:434) DECIMAL EXTERNAL ":total_orig_sales_value/10000",
weather POSITION(435:440) CHAR,
temperature POSITION(441:446) CHAR,
traffic POSITION(447:452) CHAR,
construction POSITION(453:458) CHAR,
drop_ship_ind POSITION(459:459) CHAR,
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_THEAD"
)
-- ==================================================================
INTO TABLE tdetl
WHEN (1:5) = 'TDETL'
TRAILING NULLCOLS
(sqlldr_seq "test_seq.CURRVAL",
file_type POSITION(1:5) CHAR,
file_line POSITION(6:15) INTEGER EXTERNAL,
discount_type POSITION(16:21) CHAR,
promo_tran_type POSITION(22:27) CHAR,
promotion_number POSITION(28:37) INTEGER EXTERNAL,
promo_component_number POSITION(38:47) INTEGER EXTERNAL,
coupon_number POSITION(48:63) CHAR,
coupon_ref_number POSITION(64:79) CHAR,
sales_qty POSITION(80:91) INTEGER EXTERNAL ":sales_qty/10000",
transaction_sign POSITION(92:92) CHAR,
transaction_value POSITION(93:112) DECIMAL EXTERNAL ":transaction_value/10000",
discount_value POSITION(113:132) DECIMAL EXTERNAL ":discount_value/10000",
create_date SYSDATE,
create_id "USER",
create_proc CONSTANT "LOAD_TDETL"
)
-- ==================================================================
INTO TABLE ttail
WHEN (1:5) = 'TTAIL'
TRAILING NULLCOLS
(sqlldr_seq "test_seq.CURRVAL",
position21 POSITION(21:21) CHAR)
-- tables:
SCOTT@orcl_11gR2> CREATE TABLE thead
2 (sqlldr_seq NUMBER,
3 file_type VARCHAR2(5),
4 file_line NUMBER,
5 business_date DATE,
6 transaction_date DATE,
7 location NUMBER,
8 register_id NUMBER,
9 banner_id NUMBER,
10 line_media_id NUMBER,
11 selling_item_id VARCHAR2(24),
12 cust_order_header_id NUMBER,
13 cust_order_line_id NUMBER,
14 cust_order_create_date DATE,
15 cashier_id NUMBER,
16 salesperson_id NUMBER,
17 cust_id_type NUMBER,
18 cust_id_number NUMBER,
19 tran_number NUMBER,
20 orig_reg_id NUMBER,
21 orig_tran_number NUMBER,
22 tran_header_number NUMBER,
23 revision_number NUMBER,
24 sales_sign NUMBER,
25 tran_type NUMBER,
26 sub_tran_type NUMBER,
27 retail_type VARCHAR2(1),
28 item_seq_no NUMBER,
29 employee_number NUMBER,
30 receipt_ind NUMBER,
31 reason_code NUMBER,
32 vendor_no NUMBER,
33 item_type VARCHAR2(6),
34 item VARCHAR2(25),
35 ref_item VARCHAR2(25),
36 taxable_ind VARCHAR2(1),
37 entry_mode VARCHAR2(6),
38 dept NUMBER,
39 class NUMBER,
40 subclass NUMBER,
41 total_sales_qty NUMBER,
42 total_tran_value NUMBER,
43 override_reason NUMBER,
44 return_reason NUMBER,
45 total_orig_sign VARCHAR2(1),
46 total_orig_sales_value NUMBER,
47 weather VARCHAR2(6),
48 temperature VARCHAR2(6),
49 traffic VARCHAR2(6),
50 construction VARCHAR2(6),
51 drop_ship_ind VARCHAR2(6),
52 create_date DATE,
53 create_id VARCHAR2(30),
54 create_proc VARCHAR2(10)
55 )
56 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE tdetl
2 (sqlldr_seq NUMBER,
3 file_type VARCHAR2(5),
4 file_line NUMBER,
5 discount_type VARCHAR2(6),
6 promo_tran_type VARCHAR2(6),
7 promotion_number NUMBER,
8 promo_component_number VARCHAR2(10),
9 coupon_number VARCHAR2(16),
10 coupon_ref_number VARCHAR2(16),
11 sales_qty NUMBER,
12 transaction_sign VARCHAR2(1),
13 transaction_value NUMBER,
14 discount_value NUMBER,
15 create_date DATE,
16 create_id VARCHAR2(30),
17 create_proc VARCHAR2(10)
18 )
19 /
Table created.
SCOTT@orcl_11gR2> CREATE TABLE ttail
2 (sqlldr_seq NUMBER,
3 position21 NUMBER)
4 /
Table created.
-- database sequence:
SCOTT@orcl_11gR2> CREATE SEQUENCE test_seq
2 /
Sequence created.
-- load and results:
SCOTT@orcl_11gR2> HOST SQLLDR scott/tiger CONTROL=test.ctl LOG=test.log DATA=test.dat
SCOTT@orcl_11gR2> SELECT * FROM thead
2 /
SQLLDR_SEQ FILE_ FILE_LINE BUSINESS_ TRANSACTI LOCATION REGISTER_ID
---------- ----- ---------- --------- --------- ---------- -----------
BANNER_ID LINE_MEDIA_ID SELLING_ITEM_ID CUST_ORDER_HEADER_ID
---------- ------------- ------------------------ --------------------
CUST_ORDER_LINE_ID CUST_ORDE CASHIER_ID SALESPERSON_ID CUST_ID_TYPE
------------------ --------- ---------- -------------- ------------
CUST_ID_NUMBER TRAN_NUMBER ORIG_REG_ID ORIG_TRAN_NUMBER TRAN_HEADER_NUMBER
-------------- ----------- ----------- ---------------- ------------------
REVISION_NUMBER SALES_SIGN TRAN_TYPE SUB_TRAN_TYPE R ITEM_SEQ_NO
--------------- ---------- ---------- ------------- - -----------
EMPLOYEE_NUMBER RECEIPT_IND REASON_CODE VENDOR_NO ITEM_T
--------------- ----------- ----------- ---------- ------
ITEM REF_ITEM T ENTRY_ DEPT
------------------------- ------------------------- - ------ ----------
CLASS SUBCLASS TOTAL_SALES_QTY TOTAL_TRAN_VALUE OVERRIDE_REASON
---------- ---------- --------------- ---------------- ---------------
RETURN_REASON T TOTAL_ORIG_SALES_VALUE WEATHE TEMPER TRAFFI CONSTR DROP_S
------------- - ---------------------- ------ ------ ------ ------ ------
CREATE_DA CREATE_ID CREATE_PRO
--------- ------------------------------ ----------
1 THEAD 2 14-SEP-11 14-SEP-11 209 1
1 0
16-MAY-12 SCOTT LOAD_THEAD
2 THEAD 12 14-SEP-11 14-SEP-11 209 1
1 0 -1 20110914-1
16-MAY-12 SCOTT LOAD_THEAD
3 THEAD 21 14-SEP-11 14-SEP-11 209 1
1 0
16-MAY-12 SCOTT LOAD_THEAD
3 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM tdetl
2 /
SQLLDR_SEQ FILE_ FILE_LINE DISCOU PROMO_ PROMOTION_NUMBER PROMO_COMP
---------- ----- ---------- ------ ------ ---------------- ----------
COUPON_NUMBER COUPON_REF_NUMBE SALES_QTY T TRANSACTION_VALUE DISCOUNT_VALUE
---------------- ---------------- ---------- - ----------------- --------------
CREATE_DA CREATE_ID CREATE_PRO
--------- ------------------------------ ----------
2 TDETL 13 CMPSPL 1004 0 000
000010000P000000 0000000019990000 0 2 0
16-MAY-12 SCOTT LOAD_TDETL
3 TDETL 22 EMPDSC 1005 0
0
16-MAY-12 SCOTT LOAD_TDETL
3 TDETL 23 SCOUP 1006 0
973452 0
16-MAY-12 SCOTT LOAD_TDETL
3 rows selected.
SCOTT@orcl_11gR2> SELECT * FROM ttail
2 /
SQLLDR_SEQ POSITION21
---------- ----------
1 0
2 1
3 2
3 rows selected.
-- join:
SCOTT@orcl_11gR2> SELECT h.sqlldr_seq head_seq, d.sqlldr_seq detl_seq, t.sqlldr_seq tail_seq, t.position21
2 FROM thead h, tdetl d, ttail t
3 WHERE h.sqlldr_seq = d.sqlldr_seq(+)
4 AND h.sqlldr_seq = t.sqlldr_seq(+)
5 ORDER BY head_seq
6 /
HEAD_SEQ DETL_SEQ TAIL_SEQ POSITION21
---------- ---------- ---------- ----------
1 1 0
2 2 2 1
3 3 3 2
3 3 3 2
4 rows selected.
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Mar 03 16:56:50 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
