Oracle Sequence
Moderators: chulett, rschirm, roy
Oracle Sequence
I need to get a sequence number from an Oracle sequence to use in my job. I can modify the insert/update statement in an Oracle stage to insert a sequence number, but I need that number in other stages (we are populating many tables with the same sequence number). I need to use the sequence instead of the surrogate key generator. Any suggestions for getting an Oracle sequence number to use in a job?
[quote="DSguru2B"]Do a sparse lookup, ping the sequence object and get the next key. Then save it, use it, do whatever you have to.
I do something similar in db2.[/quote]
I need to get the nextval and join it with every row of an input file, in other words, each row of the input file needs to have a new nextval value joined to it. Will a sparse lookup do that? Are you able to point me to an example?
Thanks for your help!
I do something similar in db2.[/quote]
I need to get the nextval and join it with every row of an input file, in other words, each row of the input file needs to have a new nextval value joined to it. Will a sparse lookup do that? Are you able to point me to an example?
Thanks for your help!
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
No, a sparse lookup will get the next value and the next value and the next value...
To get just one next value you can use a regular lookup (probably with Entire partitioning). Provide a constant in an additional column against which you can effect a faux comparison.
To get just one next value you can use a regular lookup (probably with Entire partitioning). Provide a constant in an additional column against which you can effect a faux comparison.
Code: Select all
SELECT 1,sequence.NEXTVAL FROM DUAL
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.
Pretty sure that's what they want.ray.wurlod wrote:a sparse lookup will get the next value and the next value and the next value...
![Confused :?](./images/smilies/icon_confused.gif)
jherr22 wrote:in other words, each row of the input file needs to have a new nextval value joined to it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- 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:
Sorry to be so vague.
Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.
Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.
Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.
Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.
Sorry to be so vague.
Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.
Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.
Here is what I need in more detail. For every row from my input dataset, I need to add a column that has the nextval from my oracle sequence. So, for example, I need to join row 1 of my dataset with sequence.nextval, row2 with sequence.nextval, row3 with sequence.nextval and soforth, where sequence.nextval will be different (of course) with each row.
Thanks again for all your help - I am relatively new with Data Stage, and this problem has us stumped.