1 Reply Latest reply: Oct 14, 2017 5:53 AM by Andrew Walker RSS

    Full outer join

    Twan Peters

      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;

        • Re: Full outer join
          Andrew Walker

          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