Hi,
My targt table has 2 fields:
CREATED_EW_DTTM and LASTUPD_EW_DTTM.
Now, I am using 'Update Existing or Insert New' action for writing to this table.
So, my aim is:
If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp.
Else if it is just an update then the CREATED_EW_DTTM shud remain the same and the LASTUPD_EW_DTTM shud be DSJobStartTimestamp.
My strategy is like this:
Before writing to the table, I shud do a reference lookup to determin what will be nature of the row (by suing DSLikkn.NOTFOUND etc.) or Use some Hashfile.
Is this the only way or there is some other way to determine the action being new or update??
Determining Update or Insert New
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
Re: Determining Update or Insert New
Well, this can be handled also by using DB constraints, means update the table key else insert the row.
But I recomend to use your strategy for perfomane reasons.
Danny
My targt table has 2 fields:
CREATED_EW_DTTM and LASTUPD_EW_DTTM.
Now, I am using 'Update Existing or Insert New' action for writing to this table.
So, my aim is:
If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp.
Else if it is just an update then the CREATED_EW_DTTM shud remain the same and the LASTUPD_EW_DTTM shud be DSJobStartTimestamp.
My strategy is like this:
Before writing to the table, I shud do a reference lookup to determin what will be nature of the row (by suing DSLikkn.NOTFOUND etc.) or Use some Hashfile.
Is this the only way or there is some other way to determine the action being new or update??[/quote]
But I recomend to use your strategy for perfomane reasons.
Danny
My targt table has 2 fields:
CREATED_EW_DTTM and LASTUPD_EW_DTTM.
Now, I am using 'Update Existing or Insert New' action for writing to this table.
So, my aim is:
If the row is new then I ll assign CREATED_EW_DTTM = DSJobStartTimestamp and LASTUPD_EW_DTTM = DSJobStartTimestamp.
Else if it is just an update then the CREATED_EW_DTTM shud remain the same and the LASTUPD_EW_DTTM shud be DSJobStartTimestamp.
My strategy is like this:
Before writing to the table, I shud do a reference lookup to determin what will be nature of the row (by suing DSLikkn.NOTFOUND etc.) or Use some Hashfile.
Is this the only way or there is some other way to determine the action being new or update??[/quote]
-
- Premium Member
- Posts: 273
- Joined: Wed Oct 18, 2006 12:20 pm
- Location: Porto
From the column names, it looks like you are using PeopleSoft 8.9 tables. If you use the DRS, you do not need to determine whether this is an update or a new row. You can choose "update or insert" or "insert or update" and the DRS will handle it for you. Look at any existing job to see how this is handled.asitagrawal wrote:kduke wrote:Not really. You have to determine which is new in the job and then insert else update. These 2 need to be 2 different links.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Perform a lookup against the target table. If the row exists it's an update; if the row does not exist it's an insert. You may find it more efficient to perform the lookup against a hashed file containing the keys from the target table.
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.