RE: SQL insert row with next sequence number

<Greg>
I need to increment a sequence number based on the values of 3 other fields.


1. If there aren't any rows for a given order number, the sequence would be 1

2. If there are rows for a given order number, I need the "next" sequence number

Currently I run Exec SQL to get the max(seq#), add one to that in RPG, and then Exec SQL and insert the row.

Just wondering if a different (more clever) method exists?

Side Note: There can be multiple users inserting rows simultaneously - this has me concerned for my method.
</Greg>

you could use a subselect in your insert statement (insert into .... select max(...) where ...). To make it transaction safe you would need commitment controll and isolation level READ STABILITY (also called locklvl(*ALL)) to ensure that max couldn't change during your insert. Don't forget to release the locks by a commit after the insert.

D*B