My code is not satisfying my conditions below
Merge into superior
superior_STAGE.superior_NUMBER not exists on superior and inactivedate is null then insert to superior
superior_STAGE.superior_NUMBER not exists on superior and inactivedate is not null then ignore
superior_STAGE.superior_NUMBER exists on superior and inactivedate is null then if any fields do not match update superior else ignore
superior_STAGE.superior_NUMBER exists on superior and no record where inactivedate is not null then update superior.ACTIVE = 'N'
can any one help me writing the code for the above conditions using merge and also I have to get the counts
Inserts;
Updates;
Set to Inactive;
Ignored;
Total superiors processed;
MERGE INTO superior a
USING (SELECT p.superior_number,
p.superior_name,
p.superior_add1,
p.superior_add2,
p.superior_add3,
p.superior_add4,
p.superior_postcode,
p.account_company,
p.superior_site_name,
p.currency_code,
p.payment_terms
FROM sif.superior_stage p
WHERE p.inactivedate IS NULL
MINUS
SELECT superior_number,
superior_name,
superior_add1,
superior_add2,
superior_add3,
superior_add4,
superior_postcode,
account_company,
superior_site_name,
currency_code,
payment_terms
FROM superior) g
ON (a.superior_number = g.superior_number)
WHEN MATCHED
THEN
UPDATE SET a.superior_name = g.superior_name,
a.superior_add1 = g.superior_add1,
a.superior_add2 = g.superior_add2,
a.superior_add3 = g.superior_add3,
a.superior_add4 = g.superior_add4,
a.superior_postcode = g.superior_postcode,
a.account_company = g.account_company,
a.superior_site_name = g.superior_site_name,
a.currency_code = g.currency_code,
a.payment_terms = g.payment_terms,
a.maint_expected = 'Y',
a.active = 'N',
a.user_code = 'STP',
a.last_modified = SYSDATE
WHEN NOT MATCHED
THEN
INSERT (
superior_number,
superior_name,
superior_add1,
superior_add2,
superior_add3,
superior_add4,
superior_postcode,
account_company,
superior_site_name,
currency_code,
payment_terms,
maint_expected,
active,
user_code,
last_modified)
VALUES (g.superior_number,
g.superior_name,
g.superior_add1,
g.superior_add2,
g.superior_add3,
g.superior_add4,
g.superior_postcode,
g.account_company,
g.superior_site_name,
g.currency_code,
g.payment_terms,
'N',
'Y',
'STP',
SYSDATE);