Full outer join

[twanqlik]

I can use some help with creating a simple 'special' full outer join.

What the goal is:

Load all the registrations from PRODUCT_REGISTRATIONS

Load only the registrations from CONSUMERS_WITH_PC_SOURCE where the %CONSUMER.KEY doesn't exist in PRDUCT_REGISTRATIONS.

Load both datasets in 1 table.

What is the most efficient way to do this?

Currently i'm Outer Joining the tables, with as result that i have consumers from which i've loaded data from the PRODUCT_REGISTRATIONS and CONSUMERS_WITH_PC_SOURCE table. That i want to avoid.

Consumers_Prod_Source:

Load

%CONSUMER.KEY,

PRODUCT_ID,

BU_CTN_KEY,

FLAG_PRODUCT_REGISTERED

Resident PRODUCT_REGISTRATIONS;

Outer Join (Consumers_Prod_Source)

Load

%CONSUMER.KEY,

BU_CTN_KEY,

1 as FLAG_SRC_PC

Resident Consumers_With_PC_Source;

[effinty2112 · Accepted solution]

Hi Twan,

Try this:

Consumers_Prod_Source:

Load

%CONSUMER.KEY,

%CONSUMER.KEY as %CONSUMER.KEY1 //temp field for use in exists function in next load

PRODUCT_ID,

BU_CTN_KEY,

FLAG_PRODUCT_REGISTERED

Resident PRODUCT_REGISTRATIONS;

Concatenate (Consumers_Prod_Source)

Load

%CONSUMER.KEY,

BU_CTN_KEY,

1 as FLAG_SRC_PC

Resident Consumers_With_PC_Source

WHERE NOT EXISTS(%CONSUMER.KEY1,%CONSUMER.KEY);

DROP FIELD %CONSUMER.KEY1;

Regards

Andrew