Sequential Function

The Sequential Function returns a number that is incremented sequentially. The syntax is:

SEQ(start, step)

start
Start value.
step
Incremental value.
  • Use the Sequential Function with character and numeric data.
  • If the locale uses a comma as the decimal separator, you must leave a space after the comma.
  • start and step are integers within the range of -2,147,483,648 and 2,147,483,647.
  • start and step are further limited by the data type and length of the destination column.
  • If the calculated value exceeds the length of the destination column, the function automatically resets to the start value.
  • When you use the Sequential Function in a concatenated expression, a variable length string is returned.

Example 1

You can use the Sequential Function to change customer data for a test database. Assume that the CUST_ID column is defined as CHAR(5). To increment by 50, starting at 1, specify:

SEQ(1, 50)

In this example, the function returns CUST_ID values starting at '00001' and increments by 50 to generate '00051', '00101', etc. When the result exceeds '99951', the function resets to the start value of 1.

Example 2

You can use the Sequential Function in a column map to mask sales data for a test database. Assume that the YTD_SALES column is defined as DECIMAL(7,2). To increment by 100 starting at 1000, specify:

SEQ(1000, 100)

In this example, the function returns YTD_SALES values starting at 1000 and increments by 100 to generate 1100, 1200, etc. When the result exceeds 99999, the function resets to the start value of 1000.

Example 3

Assume that the SALESMAN_ID column is defined as CHAR(6). To insert values beginning with ‘NJ,' followed by a number starting at 50 and incremented by 10, use the function in a concatenated expression:

'NJ'||SEQ(50, 10)

In this example, the function returns SALESMAN_ID values starting at 'NJ50 ' and increments by 10 to generate 'NJ60 ', 'NJ70 ', etc. When the result exceeds 'NJ9990', the function resets to the start value.