Surrogate Key
Moderators: chulett, rschirm, roy
Surrogate Key
Hi All,
How to generate surrogate key in server jobs? Is there any way ?
How to generate surrogate key in server jobs? Is there any way ?
Re: Surrogate Key
There is a routine under rotuines/sdk/keymgt.....You can use those ..kumar66 wrote:Hi All,
How to generate surrogate key in server jobs? Is there any way ?
RK
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
KeyMgtGetNextValue(Arg1)
===================
- Routine to generate a sequential number. This routine does not support access to a particular sequence by more than one process concurrently.
- The routine argument is the name associated with the sequence.
- The routine uses a file to store the next available number. It reads the number once, then increments and stores the value in common, writing the next value back to file each time.
or
You can create a hash to keep the maximum value of the surrogate key.
Then you create a stage variable in transformer stage beginning with zero. In derivation, verify if the stage (sk) it is zero, if it, will recover the surrogate key of the hash or else it adds one more in the stage.
===================
- Routine to generate a sequential number. This routine does not support access to a particular sequence by more than one process concurrently.
- The routine argument is the name associated with the sequence.
- The routine uses a file to store the next available number. It reads the number once, then increments and stores the value in common, writing the next value back to file each time.
or
You can create a hash to keep the maximum value of the surrogate key.
Then you create a stage variable in transformer stage beginning with zero. In derivation, verify if the stage (sk) it is zero, if it, will recover the surrogate key of the hash or else it adds one more in the stage.
Code: Select all
if sk = 0 then in.maxSurrogate else sk + 1
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54
- Joined: Wed Oct 25, 2006 11:07 pm
- Location: Hyderabad
Well if ur not comfortable with the routines, there is one more option of column generator.Say my source has 2 cols A,B to which I want to add a surrogate key column.
1.Select A,B, 1 as dummy from the source.
2.In col generator generate a dummy col with value=1 always (Set initial value=1, increament by 0) and another col INCR with initial value=part increament by part count)
3.Then based on Dummy col have a join
Now there's the problem like if job aborts!!!!! I have worked out a way for that.. But I dont know how will I be able to explain you.
Say a table has ID n Name. I make a lookup on Name to fetch the ID on the same table. provide the I/P to the Col generator which generates just a dummy (value 1 always). Get the records where ID=0 by applying filter. Join it with the dummy col (inner join) with the same source table (another instance with query like select max(id)+1 as ID, 1 as dummy from <<table>>. Feed this to a col generator which now generates an increament value(incr). Feed this to a transformer which assgins id=id+incr & then writes to the table.
Hope u understand :D
1.Select A,B, 1 as dummy from the source.
2.In col generator generate a dummy col with value=1 always (Set initial value=1, increament by 0) and another col INCR with initial value=part increament by part count)
3.Then based on Dummy col have a join
Now there's the problem like if job aborts!!!!! I have worked out a way for that.. But I dont know how will I be able to explain you.
Say a table has ID n Name. I make a lookup on Name to fetch the ID on the same table. provide the I/P to the Col generator which generates just a dummy (value 1 always). Get the records where ID=0 by applying filter. Join it with the dummy col (inner join) with the same source table (another instance with query like select max(id)+1 as ID, 1 as dummy from <<table>>. Feed this to a col generator which now generates an increament value(incr). Feed this to a transformer which assgins id=id+incr & then writes to the table.
Hope u understand :D
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 27
- Joined: Wed Jan 03, 2007 1:29 am
Since professionalism is expected. A professional will give solutions to problems rather than looking for loopholes in the questions. I believe what I have said is right?ray.wurlod wrote:Ur is a city of ancient Babylon. The second person personal pronoun is "you", not "u". We expect professionalism in communication here.
Hemachandra
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: