Generating sequence numbers using Stage variables

[yaminids]

Hello friends,

Can someone please help me with the generation of sequence numbers using Stage Variables?
In the begining of the job, we read the Max number from a table and want to generate a sequence based on the Max number
For example, if the Max number is 100 then we are interested in generating a sequence starting from 101

Any help would be highly appreciated
Thanks
Yamini

[sasi_kumarreddy]

Hi,

Take a stage variable v, assign a value to it i.e in u r case max number. Take another variable v1 and in the derivation set v1=v+1.
then use v1 for u r sequence. i guess this will work.

If not experts can correct me...

[yaminids]

Hi Sasi,

Unfortunately it didn't work. Instead of incrementing the sequence by 1 the second variable just added 1 to the first variable(Max number)

Yamini

[loveojha2]

Hi Sasi,

Unfortunately it didn't work. Instead of incrementing the sequence by 1 the second variable just added 1 to the first variable(Max number)

Yamini

If @INROWNUM=1 then v+1 Else v1+1 to v1 should work here.

[Andal]

Routine KeyMgtGetNextValue will generate the Sequence numbers. Then why you want to do it manually.

[yaminids]

Hi Anand,

As you might know that the routine KeyMgtGetNextValue will generate the sequence starting from a fixed value. I am interested in generating the sequence starting from a value which is determined during the job's execution

Yamini

[yaminids]

If @INROWNUM=1 then v+1 Else v1+1 to v1 should work here.

Thanks a lot for your help. It worked!

Yamini

[prasad v]

Hi yamini,

Can you please tell me how it works.

Actually i tried it and it has loaded only two record and then it is giving Unique constraint violation since job is trying to load duplicate sequence number.

We have created two Stage variables and in the out put column. we have derived

If @INROWNUM=1 then v+1 Else v1+1

like this.

[nani0907]

@inRownum system variable works correctly only if the execution mode is sequential.duplicates are genertaed bcoz it the job is running on multiple nodes.

[ray.wurlod]

This is a server job.

I would not bother with stage variables at all. I would pass in the maximum value as a job parameter and use the expression

Code: Select all

jpMaxKeyValue + @INROWNUM

[reddy1982]

This is a server job.

I would not bother with stage variables at all. I would pass in the maximum value as a job parameter and use the expression

Code: Select all

jpMaxKeyValue + @INROWNUM

...

[chulett]

Hello? Is there someone there?

Hmmm... could have sworn I heard something...

[ray.wurlod]

If you track back far enough in history, there's a song called "Little Sir Echo".

[qutesanju]

if you dont want to use stage variable you can go for SEQUENCE in oracle or IDENTITY in M/S SQL SERVER

[Abhijeet1980]

Yep, Agree to Ray's answer. No need to reinvent the wheel.



-Abhijit