Informatica : set statement in update

Need to update the existing records,
The record which is coming for update should be inserted and also the previous record's one column must be modified.

i have used look up to look up the data.used router to separate the data for insertion and update.
Used expression to calculate something on those new records for update.

service_code valid_startdate valid_enddate
01 2003-03-03 2004-04-03

if the new record is (01 2004-04-03 2004-04-03)coming with the same service_code,i have to insert as a new record and as well as i have to update the valid_enddate column alone.

something like the following:
service_code valid_startdate valid_enddate
01 2003-03-03 2004-04-02
01 2004-04-03 2004-04-03

If i use update strategy to insert as new records,then how can i modify the existing column.

Something like update table set old_column=new_column

buvanalogu
Profile Answers by buvanalogu Questions by buvanalogu
Dec 10th, 2007
1
2888

Questions by buvanalogu

Informatica

Answer

Showing Answers 1 - 1 of 1 Answers

anu_v_iitd
Profile Answers by anu_v_iitd

Dec 13th, 2007

Actually what need to be done here is:

You need to use two update strategy transformation just before inserting or updating the data into target table, Obviously there will be two instances of target table one for each update strategy.
Then use the property 'Insert as update' in one update strategy transformation for insert and 'update as insert' in other update strategy transformation for update. and that connect appropriate fields of target table and you can also put required conditions in update strategy transformation, when to insert or when to update.
Properties like 'Insert as update' and 'update as insert' are present in session properties of that mapping. there are some other options can be used.

I hope it will work, reply back if it is not working.

Regards,
Anupam Vishnoi