DI Studio SQL Merge Transformation User Guide

[Scott_Mitchell]

Hi All,

I am not a big user of DI Studio, but find myself in a scenario where I need to use the SQL Merge Transformation, to update and insert records in an SCD2 Oracle Table.  I have worked my way through the user guide and have really not found much information on this transformation.  I have read this article http://databobjr.blogspot.com/2011/04/load-slowly-changing-dimension-type-2.html, which has given me a lot of insight into how the code should be structured, but having taken a number of stabs at replicating the logic in DI, I have failed miserably. 

I have searched Google (of course), but I am yet to find anything that addresses this topic at length.  Does anyone have good resources around how to use this transformation at all?

Thanks in advance for any assistance offered.

Edit: This is what I have been able to come up with so far.  It appears the problem I am having is getting the subqueries to nest correctly.  This is the code I have generated so far:

MERGE INTO dim_product p
USING ( SELECT DECODE(s.scd_row_type_id,1,-6789,m.product_sid) as product_sid,
                   PRODUCT_ID,
                   PRODUCT_CATEGORY_ID,
                   PRODUCT_NAME,
                   PRODUCT_DESCRIPTION,
                   PRICE,
                   PRICE_EFFECTIVE_DATE,
                   LAST_MODIFIED_DATE,
                   CREATED_DATE,
                   m.scd_row_type_id
              FROM    (SELECT dp.product_sid,
                              sp.PRODUCT_ID,
                              sp.PRODUCT_CATEGORY_ID,
                              sp.PRODUCT_NAME,
                              sp.PRODUCT_DESCRIPTION,
                              sp.PRICE,
                              sp.PRICE_EFFECTIVE_DATE,
                              sp.LAST_MODIFIED_DATE,
                              sp.CREATED_DATE,
                              CASE
                                 WHEN dp.product_id IS NULL
                                 THEN
                                    1
                                 WHEN (dp.product_category_id !=
                                          sp.product_category_id
                                       OR dp.product_name != sp.product_name
                                       OR DP.PRODUCT_DESCRIPTION !=
                                             sp.product_description
                                       OR dp.price != sp.price
                                       OR dp.price_effective_date !=
                                             sp.price_effective_date)
                                 THEN
                                    2
                                 ELSE
                                    0
                              END
                                 AS scd_row_type_id
                         FROM    stg_product sp
                              LEFT JOIN
                                 Dim_product dp
                              ON (sp.product_id = dp.product_id and  dp.is_current_row = 'Y')
                     ) m
                   JOIN  scd_row_type s
                   ON (s.scd_row_type_id <= m.scd_row_type_id)
                   ) mp
        ON  (p.product_sid = mp.product_sid)
when matched then
   update set P.EFFECTIVE_END_DATE = mp.LAST_MODIFIED_DATE, is_current_row = 'N', updated_date = sysdate
  commit;

[rudfaden]

It unclear what the problem is

[LinusH]

I haven't used the Merge transformation that much, but it's quite straight forward - but it's' linked to the syntax of SQL MERGE statement, so you need to know what you want to do.

Why do you have to use the Merge transformation?

if you are updating a SCD Type 2 table, there's a transformation for that...