Marking a Flag as "Y"

[pradkumar]

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

[DSguru2B]

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.

[pradkumar]

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

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 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.

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

[kris]

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

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.

Should look like

NOT(outputlink.NOTFOUND)

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.

Some thing like

If NOT(outputlink.NOTFOUND) Then 'Y' Else 'N'

Hope this helps.

Kris~

[DSguru2B]

Kris is right. I said "Pass only the records which are found" which means that you need a NOT of NOTFOUND constraint.

[rafik2k]

You can also put the constraint in the transformer like

Code: Select all

sourceLink.ID1= hashLink.ID1 and sourceLink.ID2 = hashLink.ID2 And sourceLink.ID3 = hashLink.ID3 

and put derivation of Flag as 'Y'

It will pass only matching rows.
Then update only these rows at target table