Sequence reset
Moderators: chulett, rschirm, roy
Sequence reset
My table has 3 keys a,b,c.
d is a column in which i have to generate a sequence(1,2,3,...)
The sequence has to be reset when the surrogate keys (combination of keys) change.
I am able to generate the sequence but do not know how to reset it.
Any suggestions regarding this would be very helpful.
d is a column in which i have to generate a sequence(1,2,3,...)
The sequence has to be reset when the surrogate keys (combination of keys) change.
I am able to generate the sequence but do not know how to reset it.
Any suggestions regarding this would be very helpful.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
WHERE is the sequence. If you are using the SDK key managment routines, then you can update the SDKSequences file (search for details). If you're using stage variables, you can use an If..Then..Else in conjunction with change detection (the data need to be sorted by the natural key for this to work properly).
But you seem to be confused in your terminology. Surrogate keys are never reset - their sole purpose is to provide uniqueness; resetting would clearly prevent this. What you're really doing here is just a counter within each block (group) if identical grouping keys.
But you seem to be confused in your terminology. Surrogate keys are never reset - their sole purpose is to provide uniqueness; resetting would clearly prevent this. What you're really doing here is just a counter within each block (group) if identical grouping keys.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I am using Stage Variables.
I have declared 2 stage variables.
One for A and one for B.
StageVar1: If DSLink5.A = RowProcGetPreviousValue(DSLink5.A) or @INROWNUM = 1 then StageVar1+1 else 1
Var1:DSLink5.A = RowProcGetPreviousValue(DSLink5.A)
In the Output derivation for A, I am giving StageVar1.
But, i am not able to find how to do this for the columns b and c and what would be the output derivation.....
I have declared 2 stage variables.
One for A and one for B.
StageVar1: If DSLink5.A = RowProcGetPreviousValue(DSLink5.A) or @INROWNUM = 1 then StageVar1+1 else 1
Var1:DSLink5.A = RowProcGetPreviousValue(DSLink5.A)
In the Output derivation for A, I am giving StageVar1.
But, i am not able to find how to do this for the columns b and c and what would be the output derivation.....
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You need to construct pairs of stage variables, or calls for each of the key columns. The problem with RowProcGetPreviousValue is that it can only deal with one key column. Each pair of stage variables tests whether the value in that column has changed and remembers the value from the previous row (initialized to something other than null). You then need to test whether any of those three has changed in order to reset the counter in the fourth column.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How about concatenating the key columns and passing the concatenated value for the sequence generation.
Something like using the
Something like using the
Code: Select all
KeyMgtGetNextValueConcurrentBATCH(col1 : Col2 :Col3)
Success consists of getting up just one more time than you fall.