拉链表或是type2表脏数据修复问题
表名ABC
字段
ACCT_ID PROD_ID ETL_LOAD_DATE ETL_UPDATE_DATE ETL_CURR_FLAG
522 6437 2/1/2013 2/2/2013 D
522 6437 2/3/2013 2/4/2013 D
522 6437 2/7/2013 2/8/2013 U
522 6437 2/8/2013 2/9/2013 D
522 6437 2/28/2013 3/31/2013 D
ACCT_ID和PROD_ID是联合主键
要把数据修复成如下：
522 6437 2/1/2013 2/3/2013 U
522 6437 2/3/2013 2/7/2013 U
522 6437 2/7/2013 2/8/2013 U
522 6437 2/8/2013 2/28/2013 U
522 6437 2/28/2013 12/31/2999 I
U is update
D is delete
I is insert
就是把这张表按主键分组，找到最大的ETL_UPATE_DATE, 把它转换成29991231；ETL_CURR_FLAG转换成I
ETL_UPDATE_DATE 非最大的，所有ETL_CURR_FLAG转换成U；ETL_UPDATE_DATE 第一条的要跟第二条的ETL_LOAD_DATE衔接一致，像这个例子一样，按主键来查有五条，其中最大的一条是i，其她都是U，并且时间要衔接上
MERGE INTO TEST1 T1
USING (SELECT T1.*,
NVL(T2.UP_DT, '31/DEC/2099') UP_DT,
NVL(T2.UP_FLAG, 'I') UP_FLAG
FROM TEST1 T1,
(SELECT T1.*, MIN(T2.ETL_LOAD_DT) UP_DT, 'U' UP_FLAG
FROM TEST1 T2, TEST1 T1
WHERE T1.ACCT_ID = T2.ACCT_ID
AND T1.PROD_ID = T2.PROD_ID
AND T1.ETL_LOAD_DT < T2.ETL_LOAD_DT
GROUP BY T1.ACCT_ID,
T1.PROD_ID,
T1.ETL_LOAD_DT,
T1.ETL_UPDATE_DT,
T1.ETL_FLAG) T2
WHERE T1.ACCT_ID = T2.ACCT_ID(+)
AND T1.PROD_ID = T2.PROD_ID(+)
AND T1.ETL_LOAD_DT = T2.ETL_LOAD_DT(+)
AND T1.ETL_UPDATE_DT = T2.ETL_UPDATE_DT(+)) T2
ON (T1.ACCT_ID = T2.ACCT_ID AND T1.PROD_ID = T2.PROD_ID AND T1.ETL_LOAD_DT = T2.ETL_LOAD_DT)
WHEN MATCHED THEN
UPDATE SET T1.ETL_UPDATE_DT=T2.UP_DT,T1.ETL_FLAG=T2.UP_FLAG;
