Hi Team,
Need your help or suggestion on altering a merge statement.
I have a below staging table A_TRANSACTION_STAGING which gets merged to main table A_TRANSACTION :
A_TRANSACTION_STAGING :
TRANSACTION_ID NUMBER
REGION_CD VARCHAR2(5)
CHANNEL VARCHAR2(15)
GROUP VARCHAR2(15)
MATERIAL VARCHAR2(20)
PRICE DECIMAL(10,2)
COUNTRY VARCHAR2(15)
A_TRANSACTION :
TRANSACTION_ID NUMBER
REGION_CD VARCHAR2(5)
CHANNEL VARCHAR2(15)
GROUP VARCHAR2(15)
MATERIAL VARCHAR2(20)
PRICE DECIMAL(10,2)
CREATED_DATE DATE
MODIFIED_DATE DATE
Merge query :
MERGE INTO A_TRANSACTION tgt
USING (SELECT TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL, PRICE, SYSDATE AS CREATED_DATE, SYSDATE AS MODIFIED_DATE
FROM (
SELECT TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL, PRICE, ROW_NUMBER() over (partition by TRANSACTION_ID, REGION_CD, CHANNEL, GROUP, MATERIAL order by rowid desc ) last_rec FROM A_TRANSACTION_STAGING) WHERE last_rec = 1
) src
ON (tgt.TRANSACTION_ID = src.TRANSACTION_ID AND tgt.REGION_CD = src.REGION_CD AND tgt.CHANNEL = src.CHANNEL AND tgt.GROUP = src.GROUP AND tgt.MATERIAL = src.MATERIAL)
WHEN matched THEN
UPDATE SET tgt.PRICE = src.PRICE,
tgt.modified_date = src.modified_date
WHEN NOT matched THEN
INSERT (tgt.TRANSACTION_ID,
tgt.REGION_CD,
tgt.CHANNEL,
tgt.GROUP,
tgt.MATERIAL,
tgt.PRICE,
tgt.CREATED_DATE,
tgt.MODIFIED_DATE)
VALUES (src.TRANSACTION_ID,
src.REGION_CD,
src.CHANNEL,
src.GROUP,
src.MATERIAL,
src.PRICE,
src.CREATED_DATE,
src.MODIFIED_DATE);
Now the requirement is to unfold the records in staging table based on a conditional join of table B_RULE
B_RULE :
REGION_CD VARCHAR2(5)
CHANNEL VARCHAR2(15)
GROUP VARCHAR2(15)
Ex:
If A_TRANSACTION_STAGING records are :
TRANSACTION_ID|REGION_CD|CHANNEL|GROUP|MATERIAL|PRICE|COUNTRY|
158963|AMR|CUSTOMER|ONLINE|PMGHJTE|125|USA|
158964|EUR| | |OTGHYW|120|IRL|
If B_RULE records are :
REGION_CD|COUNTRY|CHANNEL|GROUP|
EUR|IRL|CUSTOMER|ONLINE|
EUR|IRL|EDUCATION|RESELLER|
Since REGION_CD value in A_TRANSACTION_STAGING is 'EUR' and COUNTRY is 'IRL' which is present in B_RULE table, i need to use all the CHANNEL & GROUP values in B_RULE while updating A_TRANSACTION . If not present in B_RULE, it should get merged as it is.
Above records should get merged into A_TRANSACTION as :
TRANSACTION_ID|REGION_CD|CHANNEL|GROUP|MATERIAL|PRICE|CREATED_DATE|MODIFIED_DATE
158963|AMR|CUSTOMER|ONLINE|PMGHJTE|125|01-NOV-2017|01-NOV-2017
158964|EUR|CUSTOMER|ONLINE|OTGHYW|120|01-OCT-2017|01-NOV-2017
158964|EUR|EDUCATION|RESELLER|OTGHYW|120|01-OCT-2017|01-NOV-2017
Further, i also need to skip insert for B_RULE matched records. It should only update the available records.
Is this possible to be handled in a single merge or should it be handled in a custom procedure.
Thanks.
So it looks to me like you just need to outer join b_rule to the working table in the using clause of merge:
create table A_TRANSACTION_STAGING (
TRANSACTION_ID NUMBER,
REGION_CD VARCHAR2(5),
CHANNEL VARCHAR2(15),
GRP VARCHAR2(15),
MATERIAL VARCHAR2(20),
PRICE DECIMAL(10,2),
COUNTRY VARCHAR2(15)
);
create table A_TRANSACTION (
TRANSACTION_ID NUMBER,
REGION_CD VARCHAR2(5),
CHANNEL VARCHAR2(15),
GRP VARCHAR2(15),
MATERIAL VARCHAR2(20),
PRICE DECIMAL(10,2),
CREATED_DATE DATE,
MODIFIED_DATE DATE
);
create table B_RULE (
REGION_CD VARCHAR2(5),
COUNTRY VARCHAR2(3),
CHANNEL VARCHAR2(15),
GRP VARCHAR2(15)
);
insert into A_TRANSACTION_STAGING values (
158963,'AMR','CUSTOMER','ONLINE','PMGHJTE','125','USA'
);
insert into A_TRANSACTION_STAGING values (
158964,'EUR',null,null,'OTGHYW','120','IRL'
);
insert into b_rule values ('EUR','IRL','CUSTOMER','ONLINE');
insert into b_rule values ('EUR','IRL','EDUCATION','RESELLER');
MERGE INTO A_TRANSACTION tgt
USING (select a.transaction_id,
a.material,
a.price,
coalesce(b.country, a.country) country,
coalesce(b.region_cd, a.region_cd) region_cd,
coalesce(b.channel, a.channel) channel,
coalesce(b.grp, a.grp) grp,
sysdate as created_date, sysdate as modified_date
from a_transaction_staging a
left join b_rule b
on a.country = b.country
and b.region_cd = b.region_cd
) src
ON (tgt.TRANSACTION_ID = src.TRANSACTION_ID AND tgt.REGION_CD = src.REGION_CD AND tgt.CHANNEL = src.CHANNEL AND tgt.GRP = src.GRP AND tgt.MATERIAL = src.MATERIAL)
WHEN matched THEN
UPDATE SET tgt.PRICE = src.PRICE,
tgt.modified_date = src.modified_date
WHEN NOT matched THEN
INSERT (tgt.TRANSACTION_ID,
tgt.REGION_CD,
tgt.CHANNEL,
tgt.GRP,
tgt.MATERIAL,
tgt.PRICE,
tgt.CREATED_DATE,
tgt.MODIFIED_DATE)
VALUES (src.TRANSACTION_ID,
src.REGION_CD,
src.CHANNEL,
src.GRP,
src.MATERIAL,
src.PRICE,
src.CREATED_DATE,
src.MODIFIED_DATE);
select * from A_TRANSACTION;
TRANSACTION_ID REGION_CD CHANNEL GRP MATERIAL PRICE CREATED_DATE MODIFIED_DATE
158963 AMR CUSTOMER ONLINE PMGHJTE 125 02-NOV-2017 04:08:39 02-NOV-2017 04:08:39
158964 EUR CUSTOMER ONLINE OTGHYW 120 02-NOV-2017 04:08:39 02-NOV-2017 04:08:39
158964 EUR EDUCATION RESELLER OTGHYW 120 02-NOV-2017 04:08:39 02-NOV-2017 04:08:39 I'm not sure what you mean by:
Further, i also need to skip insert for B_RULE matched records. It should only update the available records. But you can add where clauses to the when (not) matched clauses. So you can use this to stop inserting or updating rows as needed.
PS - please supply your tables and data in the form of "create table" and "insert into". Not only does this mean we can get an answer to you quicker, we're less likely to make a mistake building your test case.