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 Go to next message
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 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
concorde800 wrote on Wed, 16 May 2012 13:14
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

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 #554711 is a reply to message #554710] Wed, 16 May 2012 12:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I know it can be done using EXTERNAL TABLE & custom PL/SQL.
Re: How to associate a header record to its corresponding detail record [message #554712 is a reply to message #554711] Wed, 16 May 2012 13:34 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Not sure of how this can be achieved using external tables. Can you possibly give some pointers?
Re: How to associate a header record to its corresponding detail record [message #554713 is a reply to message #554712] Wed, 16 May 2012 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is SEARCH & GOOGLE broken for you?

http://www.orafaq.com/wiki/External_table
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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 #554719 is a reply to message #554718] Wed, 16 May 2012 17:14 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
I just noticed that there isn't a 1 to 1 relationship between the thead and tdetl and thead and ttail, so what I suggested is not going to match the correct records.
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.



Re: How to associate a header record to its corresponding detail record [message #554800 is a reply to message #554720] Thu, 17 May 2012 09:32 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
The creation of sequence with options(Rows=1) worked! Thanks for your assistance on this.
Re: How to associate a header record to its corresponding detail record [message #556608 is a reply to message #554800] Tue, 05 June 2012 12:36 Go to previous messageGo to next message
concorde800
Messages: 52
Registered: May 2007
Member
Is there a way to refer a column from TDETL table to obtain a value from a column in the THEAD table assuming both are in same control file?
Re: How to associate a header record to its corresponding detail record [message #556618 is a reply to message #556608] Tue, 05 June 2012 13:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
concorde800 wrote on Tue, 05 June 2012 10:36
Is there a way to refer a column from TDETL table to obtain a value from a column in the THEAD table assuming both are in same control file?


I don't think you can do this as part of the SQL*Loader process. However, you could easily join and update the tables after the load.
Re: How to associate a header record to its corresponding detail record [message #556649 is a reply to message #556618] Tue, 05 June 2012 21:01 Go to previous message
concorde800
Messages: 52
Registered: May 2007
Member
Thanks again!
Previous Topic: Escape character for loading data via sql loader
Next Topic: How to get date and time in the Date Column while sqlldr
Goto Forum:
  


Current Time: Thu Mar 28 13:04:08 CDT 2024