Increment Row value by 1

[rahulds]

I am trying to load a file into a table...whenever i load each ROW i need to increment the INTFC_ID value by 1. .how can i do this..?

[ShaneMuir]

Are you appending to the table you are loading?

If not then you can just create a counter in a stage variable.

Stage Variable = svCounter (set default value to 1)
Derivation = svCounter + 1

If you are appending then you will need to get the max INTFC_ID and start from that.

[DS_SUPPORT]

You can use @INROWNUM in your derivation directly, if you dont want to continue from the last used value.

And if you want to use from your last used value, consider writing the final value to a Hashed File, and pass the value as job parameter for the next run.

[rahulds]

You can use @INROWNUM in your derivation directly, if you dont want to continue from the last used value.

And if you want to use from your last used value, consider writing the final value to a Hashed File, and pass the value as job parameter for the next run.

@INROWNUM will not work in my case because if any ROW is rejected then the increment is not one.

[ray.wurlod]

Why did you not mention this added requirement in your original question?

Then you have no solution if this integer is your key. If this integer is not the primary key, then you could have an after insert trigger to generate its value from a sequence. DataStage can not provide something that depends on an event that occurs after DataStage has sent the row to the database server.

A better design, if you insist upon DataStage generating the integer, would be to guarantee that rows can be successfully inserted before they leave DataStage - that is, to check all security and integrity constraints within DataStage and handle the rejects before sending the row to the database server.