Oracle sequence for getting the value

adams06 · Post by **adams06** » Thu Dec 06, 2012 1:01 pm

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 in advance

chulett · Post by **chulett** » Thu Dec 06, 2012 2:21 pm

Rather than select it on every row (which you could do via a lookup) I would just use custom sql. Pass all columns except the sequence field to the target stage, let it generate the DML and then modify it to include the sequence field with a value of SEQ_NAME.NEXTVAL which tells the insert DML to automagically fetch the next value.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 06, 2012 2:23 pm

You can do that. Use user-defined SQL in the Oracle stage that provides rows to the reference input. Pass a constant (say 'X') as the "key" and, basically, ignore it in the Oracle stage.

Code: Select all

SELECT sequence.NEXTVAL AS NEXTKEY from DUAL WHERE :1 = 'X'

adams06 · Post by **adams06** » Thu Dec 06, 2012 3:49 pm

I am extremely sorry for typing in the wrong info

Here is the job design

For a every record in the source if there is a corresponding match in the lookup then we pick the id from that lookup.

else we should get the next value from oracle sequencer.

For each and every record

Thanks in advance

chulett · Post by **chulett** » Thu Dec 06, 2012 5:13 pm

OK, that only changes my answer slightly. After the lookup, branch out two links from a transformer - one for records where the lookup succeeded and another for where it failed. For the former, process as normal (I've been assuming an insert follows). For the latter, process as indicated previously.

Or alternately do another lookup to retrieve the NEXTVAL only as part of the second link.

adams06 · Post by **adams06** » Fri Dec 07, 2012 10:09 am

The nextvalue will only get incremented when records is inserted into database

So that means insert should follow for each record

Then the nextvalue will get incremented by 1

is this a true statement

chulett · Post by **chulett** » Fri Dec 07, 2012 11:23 am

If your insert DML is updated as I noted to include the reference to SEQ_NAME.NEXTVAL, then yes - the only time it will be incremented is when the insert statement is executed.

abhijain · Post by **abhijain** » Mon Dec 10, 2012 1:01 am

Perform a lookup
1. For the matched rows, Lookup will provide the ID (Say in Link A)
2. For the unmatched rows, capture the Rejects in the reject link.
2.1 Use a surrogate key generator stage and select the property
- Source Type as "Database Sequence"
- Generated Output Column Name as "your ID column"
- Source Name as "SEQ_NAME.NEXTVAL"
2.2 Output of the Surrogate key will have the new ID generated for the unmatched rows (Say in Link B)
3. Use a funnel to merge the records from Link A and Link B
4. Output of the Funnel will have ID generated for the unmatched rows and ID looked up for the matched rows.

chulett · Post by **chulett** » Mon Dec 10, 2012 8:11 am

... and you've just given a PX answer to a Server question. Please be aware of the forum / job type when answering as there are two different products in the suite.

Mike · Post by **Mike** » Mon Dec 10, 2012 8:18 am

... and also a version 8.x suggestion for a 7.x problem.

Mike

chulett · Post by **chulett** » Mon Dec 10, 2012 8:46 am

That too. Even on the PX side, not all capabilities of the 8.x suite existed back in the 7.x world.

adams06 · Post by **adams06** » Wed Dec 19, 2012 2:33 pm

Created oracle sequence, used seq.next value

Thanks for the help