DSXchange

THE SOURCE IS FROM ORACLE

ID NAME st_DATE
-- -- ---

1 MA 10-FEB-2010
1 MA 11-FEB-2010
1 MA 12-FEB-2010
2 CA 1-MAR-2010
2 CA 2-MAR-2010
2 CA 3-MAR-2010
3 NE 4-APR-2010
3 NE 5-APR-2010


O/P REQUIRED


ID NAME st_DATE KEYCHANGE
-- -- --- -----

1 MA 12-FEB-2010 1
1 MA 11-FEB-2010 0
1 MA 10-FEB-2010 0
2 CA 3-MAR-2010 1
2 CA 2-MAR-2010 0
2 CA 1-MAR-2010 0
3 NE 5-APR-2010 1
3 NE 4-APR-2010 0

My key column is ID and NAME.My requirement is for a particular key which is having maximum st_DATE i need to assign one and remaining records i need to assign
0 for that id .


I have done like this sort stage with link sort based on ID and NAME with both sorting and partition and in sort stage i used key column as st_DATE with desc with
sort key mode as-sort with create Key change column=true but i did not get required output please correct me?

Add a final Sort stage with the key change column based on sort key = ID. If your key change column is based on sort key = st_DATE then you'll get a lot of 1's and not a lot of 0's, for every time st_DATE changes values.

Is it possible to achieve this with one sort stage with the following options?

link sort based on ID and NAME with both sorting and partition
Descending on st_DATE

In sort stage key column is ID and NAME with do not sort previously sorted with create key change column=true

If NAME does not vary across records for a single ID value, your approach should work fine..

But dont forget to set Ascending order sort for ID and NAME columns and Descending order sort for st_DATE column

Try it out and let us know what you find.