How to create sequence number per ID?

bharathappriyan · Post by **bharathappriyan** » Wed Apr 05, 2006 2:30 am

Hi,

I need to create sequence number per ID.

For ex:

Input is ID column.
Output is ID and sequence number per ID.

ID Sequence_Number
100 1
100 2
101 1
102 1
102 2
102 3
102 4
103 1

If the Current records ID matches with the previous records ID, i need to increase the sequence number otherwise it should be one.

Is it possible to this in a single job itself?

Please guide me.

Thanks in Advance.

ArndW · Post by **ArndW** » Wed Apr 05, 2006 2:34 am

This easy to do using 2 stage variables in a transform stage. Declare
StageSequence as IF In.ID=LastID THEN StageSequence+1 ELSE 1
LastID as In.ID

Sunshine2323 · Post by **Sunshine2323** » Wed Apr 05, 2006 2:45 am

Hi bharathappriyan,

Declaring a stage variable

StgCounter=if RowProcCompareWithPreviousValue(Input.ID) then StgCounter+1 else 1

roy · Post by **roy** » Wed Apr 05, 2006 5:55 am

All fine guys but the real question is do you need to preserve the sequences between job runs or is every run independant and can reinitialyze the sequences?
If not then the sugested logic works fine.

aditya · Post by **aditya** » Wed Apr 05, 2006 12:07 pm

Ask the DBA to create a sequence and use it in you query. You can also create one in the ERWIN data modeller and pass it over the script to the DBA.

Thanks
Aditya.