4 Replies Latest reply: Jul 25, 2013 3:59 AM by Mikematthews-Oracle RSS

    Join two tables - column merge

    balajivenkat

      Table1 - Product ID, Description

      Table 2 - Product ID, Cost

       

      I want the result to contain Product ID, Description and Cost. Which process should I use?

       

      Balaji

        • 1. Re: Join two tables - column merge
          Mikematthews-Oracle

          Define a lookup against the table you want to check against. (Right-click on Reference Data node in the project browser, Add Lookup). The lookup can be to either staged data or external data (if the data is dynamic)

           

          Then add a Lookup and Return processor to your process and configure the options. You can check for the correct cardinality if required.

           

          If you are doing a 1:M lookup and want to create a 'join' in the process, input all the array attributes from Lookup and Return into a single Split Records from Array processor and then either Write or do other downstream processing on the joined stream.

          • 2. Re: Join two tables - column merge
            Mikematthews-Oracle

            Alternatively, you could just snapshot a view, or use SQL in the snapshot.

            • 3. Re: Join two tables - column merge
              balajivenkat

              Thank you very much Mike. I was able to join 2 tables using 'Lookup and Return' and 'Split records from Array' processors. Is it possible to join more than two tables at once using these two processors?

              • 4. Re: Join two tables - column merge
                Mikematthews-Oracle

                Generally, I would say use multiple processors for this - one for each link between tables. From a DQ perspective this is better as the lookup checks the relationship as it processes.

                 

                It is possible to use SQL in a snapshot though and lookup onto this which would enable the use of a single processor, or even no processors if all you are trying to do is bring the data in rather than check consistency etc.