Getting next key value based on highest key in target table?

[kadf88]

I've got a sequential file that I've got all ready to load into my DB except the rows in the sequential file don't have a key_id. I need to generate one for every row that gets loaded and I need the first value to be the next highest value based on the highest key already in the table.

Now I know I could get this key and put it in a hash file in a different stage and then incremement that hash file but is there a way to do it in my existing stage??

Any help is greatly appreciated, thanks

[ray.wurlod]

No. You have to persist the highest value, either locally when you run your job (use an Aggregator stage with the Last function or Max function on the key column) or by select max(key) from table; before the next run.

[DSguru2B]

Or
you can also do it in the transformer stage.
Get the max from the table, store it in a hash file and pass that value into the tranformer. Specify two stage variables

Code: Select all

var1 = IF (in.MAX=Var2) THEN Var1+1 ELSE in.MAX +1

var2 = in.MAX

your key value will be var1 in the above code.
i hope this helps
cheers.

[kcbland]

You could consider in the initialization of a stage variable using an ExecSH/ExecDOS call to a database command line program like sqlplus to issue SELECT MAX(... SQL statement on your table and return the answer as the initialization value. Increment your stage variable from there with each use.

[kadf88]

Or
you can also do it in the transformer stage.
Get the max from the table, store it in a hash file and pass that value into the tranformer. Specify two stage variables

Code: Select all

var1 = IF (in.MAX=Var2) THEN Var1+1 ELSE in.MAX +1

var2 = in.MAX

your key value will be var1 in the above code.
i hope this helps
cheers.

hmm, that might help, I'll try that, thanks!