Hi There,
I have three tables to match up with data.
TABLE-A is source table (do not have ADDRESS_KEY). I need to insert records on TABLE-B on below conditions:
If TABLE-A.PARTY_KEY=TABLE-C.PARTY_KEY and TABLE-A. ADDRESS_TYPE_CD= TABLE-C. ADDRESS_TYPE_CD
THEN insert TABLE-C record in TABLE-B
If TABLE-A record does not exists in TABLE-C ( in this case record P2760 PR, does not exists on TABLE-C)
Then insert a new record in TABLE-B (with a new address_key)
TABLE-A
PARTY_KEY ADDRESS_TYPE_CD
========= ===============
P1234 PRIMARY
P2760 PR
P7777 PRIMARY
TABLE-B
ADDRESS_KEY PARTY_KEY ADDRESS_TYPE_CD
=========== ========= ================
AD-KEY-1 P1234 PRIMARY
AD-KEY-2 P7777 PRIMARY
AD-KEY-3 P2760 PR
TABLE-C
ADDRESS_KEY PARTY_KEY ADDRESS_TYPE_CD
=========== ========= ================
AD-KEY-1 P1234 PRIMARY
AD-KEY-2 P7777 PRIMARY
CREATE TABLE TABLE-A
(
PARTY_KEY VARCHAR2(50 CHAR),
ADDRESS_TYPE_CD VARCHAR2(50 CHAR)
);
CREATE TABLE TABLE-B
(
ADDRESS_KEY VARCHAR2(50 CHAR),
PARTY_KEY VARCHAR2(50 CHAR),
ADDRESS_TYPE_CD VARCHAR2(50 CHAR)
);
CREATE TABLE TABLE-C
(
ADDRESS_KEY VARCHAR2(50 CHAR),
PARTY_KEY VARCHAR2(50 CHAR),
ADDRESS_TYPE_CD VARCHAR2(50 CHAR)
);
FYI – I am using below sequence and function to create address_key.
create sequence s_addr_key start with 1;
create or replace function AddressKeyNext return varchar2 is
begin
return 'AD-KEY-' || s_addr_key.nextval;
end;
Thank you in advance!