{"id":1823,"date":"2018-02-21T12:10:28","date_gmt":"2018-02-21T11:10:28","guid":{"rendered":"http:\/\/van-maanen.com\/?p=1823"},"modified":"2018-02-21T12:10:28","modified_gmt":"2018-02-21T11:10:28","slug":"scd-2-and-the-oracle-merge-statement","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1823","title":{"rendered":"SCD-2 and the Oracle Merge statement"},"content":{"rendered":"

Yesterday I found a very nice script that allows processing records in a target table with a SCD-2 mechanism. The script is very elegant. It contains several logical steps that are knitted together in one SQL statement.<\/p>\n

Th mechanism of SCD2 is as follows. First, a comparison is made between a source table and a target table. One may encounter two situations: one whereby a situation\/ record is provided that is completely new and a situation\/ record that is in reality an update from a previous situation.<\/p>\n

When a new situation is provided, a new record must be inserted into the target table. When an update is provided, a record in the target table must be updated with an end date that indicates that the record can be seen as being a description of history. Moreover in case of an update a record must be inserted.<\/p>\n

Let us look at an example. We start with:<\/p>\n

\"\"<\/p>\n

 <\/p>\n

Id stands for the identification of a situation, that can be updated. Naam is a field that can be updated. sk is a surrogate key that acts as a primary key. Startdate is the moment of an insert; enddate is the point in time until when the description is valid.<\/p>\n

Let us assume, we have new data that look like
\n\"\"<\/p>\n

, we may derive that the final version will look like:<\/p>\n

\"\"<\/p>\n

We see tom and ine being unchanged. The Stella record is finished with an end date (21-02-2018). Paula and Tessa are inserted with a startdate and an end date that is set at 31-12-99.<\/p>\n

 <\/p>\n

The script reads as:<\/p>\n

MERGE INTO mergedoel p\nUSING \n ( SELECT DECODE(s.scd_row_id,1,0,m.sk) as sk,\n                   ID,\n                   NAAM,\n                   m.scd_row_id\n   FROM    (SELECT dp.sk,\n                   sp.ID,\n                   sp.NAAM,\n                   CASE\n                     WHEN dp.id IS NULL\n                       THEN 1\n                     WHEN (dp.naam != sp.naam) \n                       THEN 2\n                       ELSE 0 \n                   END AS scd_row_id\n                   FROM    mergebron sp\n            LEFT JOIN mergedoel dp  \n            ON (sp.id = dp.id and  dp.enddate > sysdate)\n           ) m\n   JOIN  scd_row_type s\n   ON (s.scd_row_id <= m.scd_row_id)\n ) mp\nON  (p.sk = mp.sk)\nwhen matched then\n   update set P.enddate = sysdate\nwhen NOT matched then\n   insert (P.ID,P.NAAM,P.SK, p.startdate , p.enddate)\n   values (mp.id,mp.naam,S_MERGE.nextval,sysdate, to_date('2099-12-31 00:00:00','YYYY-MM-DD HH24:MI:SS'));\n  commit;\n<\/pre>\n
and the tables that we need are:<\/p>\n
\nCREATE TABLE SCD_ROW_TYPE\n(\nSCD_ROW_ID NUMBER NOT NULL,\nSCD_ROW_DESCRIPTION VARCHAR2(20 CHAR)\n);\nInsert into scd_row_type(scd_row_id,scd_row_description) values(1,'For Insert');\nInsert into scd_row_type(scd_row_id,scd_row_description) values(2,'For Update');\n\n\n\nCREATE TABLE \"MERGEBRON\" \n   (\t\"ID\" NUMBER NOT NULL ENABLE, \n\t\"NAAM\" VARCHAR2(20 BYTE)\n   );\n\n\nCREATE TABLE \"MERGEDOEL\" \n   (\t\"ID\" NUMBER NOT NULL ENABLE, \n\t\"NAAM\" VARCHAR2(20 BYTE), \n\t\"SK\" NUMBER NOT NULL ENABLE, \n\t\"STARTDATE\" DATE, \n\t\"ENDDATE\" DATE\n   ); \n\n<\/pre>\n
The logic of this procedure is that is a first step a comparison is made between the target table and the source table. If no match is found between the target table and source table (based on id), a record is inserted.
\nIf a match is found but the descriptions differ, two actions will be taken: records with end date are set at the system date and a new record are inserted.
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Yesterday I found a very nice script that allows processing records in a target table with a SCD-2 mechanism. The script is very elegant. It contains several logical steps that are knitted together in one SQL statement. Th mechanism of SCD2 is as follows. First, a comparison is made between a source table and a […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1823","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1823"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1823\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1823"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}