DSXchange

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

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.

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.

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

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.

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

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.

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.

... 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.

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

Mike

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

Created oracle sequence, used seq.next value

Thanks for the help