Sequence reset

[kris_r]

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.

[kris_r]

Sorry for the post. I did a search and was able to find the answer.

[kris_r]

My Problem is i do not know how to reset the value of d (which will be a sequence) once the combination of my primary keys changes.

[ray.wurlod]

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.

[kris_r]

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.....

[ray.wurlod]

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.

[kris_r]

Is there any function similar to RowProcGetPreviousValue which can be used for multiple fields or if i have to use stage variables?
In case of stage variables how do i reset the sequence based on the 3 keys(3 stage variables)..

[ray.wurlod]

No.

You could always create one. More arguments, more variables in a COMMON area.

[kris_r]

I am declaring different stage variables for each key column.
But, how do I use these stage variables in the Output derivation...

[loveojha2]

How about concatenating the key columns and passing the concatenated value for the sequence generation.

Something like using the

Code: Select all

KeyMgtGetNextValueConcurrentBATCH(col1 : Col2 :Col3)

[kris_r]

Is thos a built in function?? I am not able to use this function..

[kris_r]

Where do i use this UPDATE statement?

[kumar_s]

Given in the previous post.

...using a TCL command(Exec TCL) in a before-job subroutines...