Рет қаралды 1,406
#iics #scdt1 #etlguru
SCD Type1 Implementation in IICS using MD5 checksum
SRC
EMP_ID,EMP_NAME,EMP_SALARY,DEPT_ID
100,Naganth,70000,10
101,Vinod,60000,10
102,Antosh,50000,11
103,Krishna,90000,12
104,Mahadev,80000,13
TGT
CREATE TABLE EMPLOYEES_SCDT1(
emp_key number(6),
emp_id number(6),
emp_name varchar2(20),
emp_salary number(8,2),
emp_dept_id number(4),
checksum varchar2(50));
Checksum Expression
MD5(EMP_ID||EMP_NAME||EMP_SALARY||DEPT_ID)
Lookup Query
SELECT EMP_KEY AS LKP_EMP_KEY,
EMP_ID AS LKP_EMP_ID ,
CHECKSUM AS LKP_CHEKSUM FROM EMPLOYEES_SCDT1;
Expression for flag
IIF(ISNULL(LKP_EMP_KEY),'I', IIF(o_checksum!=LKP_CHEKSUM,'U'))