Skip to Main Content
  • Questions
  • SQL Loader header row attribute cannot be applied to detail rows in event of multiple header/detail transactions

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Vijay.

Asked: June 10, 2021 - 12:15 pm UTC

Last updated: June 11, 2021 - 12:57 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

How to load using Oracle SQL Loader values to it's related detail rows if the file has multiple headers and details rows. The issue is header row attribute cannot be applied to it's related detail rows in event of multiple header/detail transactions. It does works and load for 1 header, where header loader evaluation is done for very first header and applies to all detail rows, which is not expected to happen.

I need to apply each header row attribute value to applied it's child detail rows.

Here is the example. Each H - Header Record attribute value 1001, 1002 and 1003, I need to stamp to each respective detail record while loading via SQL Loader.

H ABC 1001
D XYZ 89.90
D XYZ 89.91
D XYZ 89.92
H ABC 1002
D XYZ 89.90
D XYZ 89.91
D XYZ 89.92
H ABC 1003
D XYZ 89.90
D XYZ 89.91
D XYZ 89.92

The expected results should be in database table after SQL loader is completed as follows, which does not happen. Any suggestions!

H ABC 1001
D XYZ 89.90 1001
D XYZ 89.91 1001
D XYZ 89.92 1001
H ABC 1002
D XYZ 89.90 1002
D XYZ 89.91 1002
D XYZ 89.92 1002
H ABC 1003
D XYZ 89.90 1003
D XYZ 89.91 1003
D XYZ 89.92 1003

Thank you.

and Chris said...

You can load records into different tables using the when clause. For example:

INTO TABLE h
WHEN tab = 'H'
FIELDS TERMINATED BY WHITESPACE 
(
tab filler, c1, c2
)
INTO TABLE d
WHEN tab = 'D'
FIELDS TERMINATED BY WHITESPACE
TRAILING NULLCOLS
(
tab filler position(1), c1, c2, c3
)


But I'm not aware of a way to get values from previous records in the load.

To do this, you could either:

- Load the data into a generic staging table first
- Read the file using an external table

You can then get the last H values with something like:

with rws ( rn, tab, v1, v2 ) as (
  select 1, 'H', 'ABC', 1001 from dual union all
  select 2, 'D', 'XYZ', 89.90 from dual union all
  select 3, 'D', 'XYZ', 89.91 from dual union all
  select 4, 'D', 'XYZ', 89.92 from dual union all
  select 5, 'H', 'ABC', 1002 from dual union all
  select 6, 'D', 'XYZ', 89.90 from dual union all
  select 7, 'D', 'XYZ', 89.91 from dual union all
  select 8, 'D', 'XYZ', 89.92 from dual union all
  select 9, 'H', 'ABC', 1003 from dual union all
  select 10, 'D', 'XYZ', 89.90 from dual union all
  select 11, 'D', 'XYZ', 89.91 from dual union all
  select 12, 'D', 'XYZ', 89.92 from dual 
)
  select tab, v1, v2,
         case when tab = 'D' then
           last_value ( 
             case when tab = 'H' then v2 end 
          ) ignore nulls over ( 
            order by rn 
          )
         end h1_v1
  from   rws;
  
TAB    V1          V2      H1_V1    
H      ABC        1001    <null> 
D      XYZ        89.9      1001 
D      XYZ       89.91      1001 
D      XYZ       89.92      1001 
H      ABC        1002    <null> 
D      XYZ        89.9      1002 
D      XYZ       89.91      1002 
D      XYZ       89.92      1002 
H      ABC        1003    <null> 
D      XYZ        89.9      1003 
D      XYZ       89.91      1003 
D      XYZ       89.92      1003 


Note to ensure this works correctly, you need row numbers that correspond to the record's position in the file. You can use recnum in SQL*Loader to assign this.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Sr. Database Developer

Vijay Kodia, June 11, 2021 - 2:38 pm UTC

Thanks Chris for your help on this issue. I was trying to load and work with in single table itself. But it looks like two tables is way to go at this time. Appreciated.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.