DSXchange

Hi
I have been working with SCD2 and have a difficulty in updating the old record and changing the delete flag on the deleted records.

We have 3 columns that track every insert/ update

for insert:
eff_date=sysdate
exp_date=null
delete_flag=No

for update:
insert new record
eff_date=sysdate
exp_date=null
delete_flag=No

old record
change exp_date=sysdate-1

for delete:
update old record
delete_flag=Yes

How will be implement this.

I am using Change capture stage but donot know
1. how to get the
keys for the old record that needs to be updated
2. Change the exp_date value to sysdate-1

For delete how to change the delete flag to yes to that particular record.

I would appreciate if you could help me by specifying what stages to use and how to implement the logic in them.

Thanks
Bob

Getting the key should have been done upstream of the Change Capture stage via a lookup against a "business key to surrogate key" data set.

Varying the delete flag or end date then becomes a straightforward update.