%Next and %Previous meta-SQL element

Description

Use the %Next and %Previous functions to return the value of the next or previous field in a numbered sequence. These functions are valid in any Application Engine SQL action and should be used when performing sequence-numbering processing. Typically, you use them instead of a %Bind construct. These functions use the current value of the number field as a bind variable and then increment (%Next) or decrement (%Previous) the value after the statement runs successfully. A number field indicates the numeric field of the state record that you initially set to a particular value (as in 1 to start).

If the statement is a Select and no rows are returned, the field value is not changed. The substitution rules are the same as for %Bind. For example, if the ReUse property is enabled, then the field is a true bind (':n' substituted). Otherwise, inline substitution occurs.

Example

You could use these functions in an Update statement within a Do Select action:

• Do Select action

  %SELECT(field1, field2, ...) SELECT key1, key2, ... FROM PS_TABLE WHERE ... 
  ORDER BY key1, key2, ..."

• SQL

  UPDATE PS_TABLE SET SEQ_NBR = %Next(seq_field) WHERE key1 = %Bind(field1) 
  AND key2 = %Bind(field2) ...

With a Do Select action, the increment/decrement occurs once per run, not once for every fetch. So unless your Do Select action implements the Reselect property, the value is changed only on the first iteration of the loop. Alternatively, with the Reselect property or Do While and Do Until actions, every iteration reruns the Select statement and then fetches one row. With these types of loops, the value changes on every iteration.