SELF_UPDATE ETL Transformation pattern

DATA ARCHITECTURE

by Ludek Bob Jankovsky, 02-Sep-2008 (ETL PATTERNS)

Self update patterrn is one of simpliests patterns. It performs updates based just on record data, such as closing record fulfilling specified conditions etc.

Metadata example:

<?xml version="1.0"?>
<MODULE Pattern="SELF_UPDATE" TargetOwner="A2" TargetTable="PRODUCT_INSTANCES">
<COLMAP TargetColumn="PRODI_REVOLVING_FLAG">'N'</COLMAP>
<COLMAP TargetColumn="PRODI_UPDATED_DATETIME" DirectFlag="Y" UpdateFlag="Y">sysdate</COLMAP>
<COLMAP TargetColumn="PRODI_UPDATE_PROCESS_KEY" DirectFlag="Y"
UpdateFlag="Y">P_PROCESS_KEY</COLMAP>
</MODULE>

Results in following statement:

UPDATE A2.PRODUCT_INSTANCES PARTITION(PARTITION_501) TRG$$
SET
TRG$$.PRODI_REVOLVING_FLAG='N'
,TRG$$.PRODI_UPDATED_DATETIME=sysdate
,TRG$$.PRODI_UPDATE_PROCESS_KEY=P_PROCESS_KEY
WHERE (TRG$$.PRODI_REVOLVING_FLAG!='N'
)--/differential part
;

Ludek Bob Jankovsky