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!
lookup and (possible) update a table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 503
- Joined: Wed Jun 29, 2005 8:14 am
Hi,
Do a look up based on the two columns. One is fund code and other Fundname.
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.