PeopleSoft Wiki

To add a new sequence number based on existing key structures where the sequence number increments based on those keys the following query logic applies. This would be used when there is data already present and PeopleCode will not be triggered to set the sequence number.

How this query works is to group results based on keys and then find the count of rows with a rowid greater than or less than its rowid.

UPDATE RECORD_NAME T1
  SET T1.SEQNUM_FIELD= (
    SELECT Count(*)
    FROM RECORD_NAME T2
    WHERE
      T1.KEY_FIELD_1 = T2.KEY_FIELD_1 
      AND  T1.KEY_FIELD_2 =T2. KEY_FIELD_2
      AND  T2.ROWID >= T1.ROWID 
    GROUP BY T2.KEY_FIELD_1, T2.KEY_FIELD_2 
)