Posted Thu, 29 Oct 2020 01:32:45 GMT by

Hoping someone can help me. I have a view that is pulling rates by customer. Each customer will have multiple records. I want to assign a row number for each rate record per customer. So, customer ABC having 3 rates will show results with numbers 1,2,3. Customer DEF having 6 rates will show results numbered 1,2,3,4,5,6... and so on. I know how to do an ACCUM function, but it accumulates across ALL records, tried grouping but it didn't change my results. 

Is there a way to make this work?

Thanks - 

Diana W.

Posted Thu, 29 Oct 2020 02:35:32 GMT by

Hi Diana,

Try this.

ACCUM(1,xyz),  where xyz is the key field that you want to sequence.  Make sure xyz is a CHAR type field.

The accum function will accumulate by 1 for each row, and reset when xyz changes or breaks.

It's ok to sort on xyz, but don't use any aggregate or grouping functions with ACCUM.  



You must be signed in to post in this forum.