Adding Sequence Numbers
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
)
No Comments