DSXchange

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?

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.

SELECT sequence_name.CURRVAL FROM dual

If you run this select, it will return the current value of the Oracle Sequence.

Regards
Richard

Which doesn't help all that much as you really want to select the NEXTVAL so it gets incremented properly.

[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!

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.

ray.wurlod wrote:a sparse lookup will get the next value and the next value and the next value...

Pretty sure that's what they want.

jherr22 wrote:in other words, each row of the input file needs to have a new nextval value joined to it.

Yet

jherr22 wrote:I need to get the nextval and join it with every row of an input file

could be read the other way.

I know. Hence the

indeed!

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.

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.

Has or had? Sparse lookup.

Thank you all for your help - you guys rock!

I did not realize that the sparse lookup was an option in the Oracle stage - I was looking in the lookup stage. Works great now!

Thanks!