DSXchange

Hi, First job is inserting 10 records in one table with increment of Temp_Id like 1,2,3.......10
The second job need to insert 20 more records in the same table with increment of Temp_Id by taking the last value of the first insert like 11,12,......30

Appreciate your help on this.Thanks

There are a couple of ways of accomplishing this.

1. Create a sequence/trigger within your database to automatically assign a value to the Temp_id when the values are loaded; or

2. Use a bit a sql code to retrieve the current max(temp_id) from the that table. Pass this value to a surrogate key generator stage and use that to create your temp_id, using the max(temp_id) as your starting point (pass it in as a parameter).

OK... how are you generating this "temp_id" value currently? Investigate the use of the Surrogate Key Generator stage and the use of a "state file", that's the "official" way to handle stuff like this.

Earlier i did this sequence number by using stage variable in transformer.but now i need to take max value of that and need to process it again to another job/table.

Earlier i did this sequence number by using stage variable in transformer.but now i need to take max value of that and need to process it again to another job/table.

Can you not do something like Shane posted as #2 above - retrieve the max value using SQL and pass that as a job parameter that is the starting value for the job run.

I am getting an eroor while trying to get max(col) through sql in source stage. is there any way to get the max value

Error executing View Data command:##E IIS-DSEE-TOPK-00007 15:19:17(010) <_PEEK_IDENT_> Input dataset does not have field:

Are you able to get any value when you execute the same query in toad or sqlplus ??

Yes i am getting the max val of that col when executed but not in datastage source stage. server stage is working fine only the issue with parallel oralce stage.

Using the parallel extender you cannot get a serial number. You need to use the database sequence features.

Regards
Sreeni