Generating same sequence number for a similiar group values

[dstest]

Hi,

I have a input data like below

ID,ID1
5,100
5,200
5,300
6,400
6,500
6,600
1,600
1,700

I need the output like below.

SEQID,ID,ID1
1,5,100
1,5,200
1,5,300
2,6,400
2,6,500
2,6,600
3,1,600
3,1,700

For similar IDs i need to assing same sequence number.Can any one please help me how to implement this logic in datastage or oracle (sql) .

Thanks

[ArndW]

You can use a stage variable to store the last ID and compare that with the current one to see if it has changed. Note that in an EE job you will need to partiiton on ID in order to ensure that this works correctly.

[ray.wurlod]

Run your data through a Sort stage to generate a Key Change column (which is 1 if the sort key changes, 0 otherwise). Use this value in a downstream Transformer stage it increment a stage variable (that was initialized to 0) if the key change column is 1.