Page 1 of 2
Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 12:08 am
by 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
Re: Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 12:20 am
by 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...
Re: Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 12:24 am
by 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
Re: Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 12:39 am
by loveojha2
yaminids wrote: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.
Posted: Mon Dec 12, 2005 12:44 am
by Andal
Routine KeyMgtGetNextValue will generate the Sequence numbers. Then why you want to do it manually.
Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 12:50 am
by 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
Re: Generating sequence numbers using Stage variables
Posted: Mon Dec 12, 2005 1:16 am
by yaminids
If @INROWNUM=1 then v+1 Else v1+1 to v1 should work here.
Thanks a lot for your help. It worked!
Yamini
Posted: Tue Apr 20, 2010 4:19 am
by 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.
Posted: Tue Apr 20, 2010 5:03 am
by 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.
Posted: Tue Apr 20, 2010 5:12 am
by 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
Posted: Mon Apr 26, 2010 5:45 am
by reddy1982
ray.wurlod wrote: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
...
Posted: Mon Apr 26, 2010 6:19 am
by chulett
Hello? Is there someone there?
Hmmm... could have sworn I heard something...
Posted: Mon Apr 26, 2010 6:28 am
by ray.wurlod
If you track back far enough in history, there's a song called "Little Sir Echo".
Posted: Mon Apr 26, 2010 7:14 am
by qutesanju
if you dont want to use stage variable you can go for SEQUENCE in oracle or IDENTITY in M/S SQL SERVER
Posted: Mon Apr 26, 2010 7:20 am
by Abhijeet1980
Yep, Agree to Ray's answer. No need to reinvent the wheel.
-Abhijit