Forum Stats

  • 3,722,431 Users
  • 2,244,307 Discussions
  • 7,849,830 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Three tables to match up with data

User_6GLH0
User_6GLH0 Member Posts: 24 Green Ribbon

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:

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

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

Sign In or Register to comment.