BookmarkSubscribeRSS Feed
Scott_Mitchell
Quartz | Level 8

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;

 

2 REPLIES 2
rudfaden
Pyrite | Level 9
It unclear what the problem is
LinusH
Tourmaline | Level 20

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

Data never sleeps

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 548 views
  • 0 likes
  • 3 in conversation