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.
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.