lookup and (possible) update a table

[fyaqq]

I have a table called fund_info where I store fund related data like fund code, fund name, start_date, end_date etc. When a new record comes, I need to first check if it exists in the table by a primary key. If not, I know it is a new record and insert it into the table. Otherwise, it's already in the table. But the situation is it is same fund but with a different name. In this case I need to change the end_date of the old record to current date and insert a new record , whose start_date is the current date, and the end_date is null.

I was wondering what is the best way to accomplish this?

Thank you very much!

[DeepakCorning]

Many ways to do it. One way that I can think of is to have a target table lookup with key columns and one target table lookup with all the columns (hopefully the tabel that you are working on is small) and depending on which lookup fails set a flag and assign the values depending on this flag.

[meena]

Hi,
Do a look up based on the two columns. One is fund code and other Fundname.

store fund related data like fund code, fund name, start_date, end_date etc. When a new record comes, I need to first check if it exists in the table by a primary key. If not, I know it is a new record and insert it into the table

[ray.wurlod]

Welcome aboard. :D

You are attempting to implement what is known as a "slowly changing dimension". A search for this term, and/or for "SCD", will yield discussion and techniques for implementing same in DataStage.

[fyaqq]

Thank you for all the input!