Hi
I am having a requirement where I need to update a Flag to "Y" for most recent record and the od record should be amrked as "Y"
My table has the following columns
ID1 ID2 ID3 ID4 Name Description Flag
1 20 30 40 XYZ Travel
1 20 31 40 YZX Travel
1 25 31 50 UXY Expenses
ID1,ID2,ID3 together forms the primary key for my table.
Now I need to update the flag to "Y" for the max of these IDS ie for the set (1,25,31) the flag should be "Y" and for remaining it should be "N".
I am lanning to do one time update for this. But as my process runs daily incremenatlly, I have to handle it in Data Stage
Lets say today new record (1,26,31) comes in, so I need to mark the previous record (1,25,31) to "N" and mark the new record as "Y"
Please share your ideas on this topic
Thanks
Marking a Flag as "Y"
Moderators: chulett, rschirm, roy
Marking a Flag as "Y"
Pradeep Kumar
The way I see it, it really depends upon ID1. Do a group by on ID1 and get the max of ID2 and ID3. Is that correct?
If yes then pass that sql and create a hashed file that has only the first three keys. This hashed file will now contain all the keys that need to be set to Y.
In one job take ID1 from this hashed file and update your table with flag set to 'N'.
In second job do a simple select from your table and do a lookup against this hashed file. Pass only the records that are found with flag set to 'Y' and update your table. This should be good for your incremental load.
If yes then pass that sql and create a hashed file that has only the first three keys. This hashed file will now contain all the keys that need to be set to Y.
In one job take ID1 from this hashed file and update your table with flag set to 'N'.
In second job do a simple select from your table and do a lookup against this hashed file. Pass only the records that are found with flag set to 'Y' and update your table. This should be good for your incremental load.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Thanks for your reply DSGuru.
I did the first part using a simple update statement and set all records to "N"
Coming to the second part
I am hardcoding Current_IND on the target side with "Y", if these IDs match with hash file.
But unfortunately all hte records are getting updated to "Y"
Could anyone guide me where I am going wrong
Thanks
I did the first part using a simple update statement and set all records to "N"
Coming to the second part
I did design a job using hash file as lookup and I selected from the source all the three columns (ID1, max(ID2), max(ID3) grouped by ID1) and doing lookup.In second job do a simple select from your table and do a lookup against this hashed file. Pass only the records that are found with flag set to 'Y' and update your table. This should be good for your incremental load.
I am hardcoding Current_IND on the target side with "Y", if these IDs match with hash file.
But unfortunately all hte records are getting updated to "Y"
Could anyone guide me where I am going wrong
Thanks
Pradeep Kumar
Do you have the right constraint in the transformer for the output link? which will only write record to the out put link that has matching rows.pradkumar wrote:I am hardcoding Current_IND on the target side with "Y", if these IDs match with hash file.
But unfortunately all hte records are getting updated to "Y"
Could anyone guide me where I am going wrong
Thanks
Should look like
If you need to write all the rows to the output link, then you need a logical if--else condition in the derivation of the column to handle this.NOT(outputlink.NOTFOUND)
Some thing like
Hope this helps.If NOT(outputlink.NOTFOUND) Then 'Y' Else 'N'
Kris~
~Kris
You can also put the constraint in the transformer like
and put derivation of Flag as 'Y'
It will pass only matching rows.
Then update only these rows at target table
Code: Select all
sourceLink.ID1= hashLink.ID1 and sourceLink.ID2 = hashLink.ID2 And sourceLink.ID3 = hashLink.ID3
It will pass only matching rows.
Then update only these rows at target table