Sequence_value()
Returns a sequential value for a field.
Syntax
key = [callproc] sequence_value
( table_name = table_name , column_name =
column_name [, increment = increment, start_value
= start_value])
key
Specifies an integer variable that holds the sequence value generated
table_name
Specifies a string expression whose value is the name of the table for which sequence_value() generates a key value. The table must already exist in the database.
column_name
Specifies a string expression whose value is the name of the column for which sequence_value() generates a key value. The column must already exist in the database.
increment
Specifies a positive integer that specifies the number of sequence values to be returned and added to the value stored in the table. Optional. Defaults to 1.
start_value
Specifies a positive integer that tells where to start generating sequence keys for an empty table, if sequence_value has never been called before for this table/column combination. Optional. Defaults to 1.
Description
The 4GL sequence_value() statement returns a positive integer that is automatically incremented whenever a value is fetched. You can use the sequence value as a surrogate key to assign to a row of a table or insert into other rows in other tables as a join attribute. This calling sequence operates like a 4GL procedure.
The sequence_value() procedure returns a positive integer that is a new sequence value (or the highest of a range of sequence values) for a unique input table and column.
The sequence_value() function generates sequential values by retrieving the next row from an extended system catalog called ii_sequence_values. This catalog contains a row for each unique table/column pair for which sequence values are defined.
The following table shows the attributes of ii_sequence_values:
sequence_owner
varchar(32). Owner of a table. Not nullable.
sequence_table
varchar(32). Name of a table. Not nullable.
sequence_column
varchar(32). Name of column in a table. Not nullable.
sequence_value
integer. Not nullable.
If a record exists in the catalog, the sequence_value() procedure increments it and returns the result. If not, the function increments the current maximum value of the column in the table.
If the table contains no rows which have non-null values for the column, it uses the start_value instead. If a record does not exist in the catalog, sequence_value() adds one. The default is 1. The return of a negative integer or 0 indicates an error.
Consider the following issues when using the sequence_ value() function:
• If you insert any rows to the table without using the sequence_value() function--for example, through QBF or the Terminal Monitor--these rows cannot have sequential key values, because only the sequence_value() function retrieves the next value from the ii_sequence_values catalog.
To prevent non-unique values, the database table must have a structure of btree unique.
• There is no 3GL equivalent of this function; therefore, you cannot use a 3GL procedure to generate sequence values.
Transaction Handling with Sequence_value()
Your table can have gaps in the sequence of the values that sequence_value() generates. This is because the sequence value is returned before the insert is executed. Thus the ii_sequence_values catalog is updated even if the insert is not performed. If it is important that values be sequential with no gaps or duplicate values, place the sequence_value() and insert statements within the same transaction. Autocommit must be off.
However, it is not be efficient to do this when you have concurrent users and your insert statement takes a long time to execute. This is because the ii_sequence_values catalog is locked until the entire transaction is executed. If one user is adding new rows, other users trying to add rows at the same time must wait until the first transaction is completed.
Do not place the sequence_value() function and insert statement within the same transaction if you want to display the sequence value on the form. This is because the ii_sequence_values catalog remains locked until you actually save the new row to the database.
Sequence Values and System-Maintained Keys
Using the sequence_value() function lets you generate surrogate keys for rows that you add to a database table. You also can create unique keys by defining a column of the table to be a system-maintained key (described in the SQL Reference Guide).
The following table compares system-maintained keys and values generated by the sequence_value() function:
Example
The following statement shows the use of sequence_value() to obtain a surrogate key in the Employees table.
key = sequence_value
(table_name = 'employees',
column_name = 'employee_number' );
commit;
if (key > 0) then
insert into employees
(employee_number)
values ( :key );
commit;
When generating a large number of values, locking can occur. The following statement allows for generating 1000 numbers:
high = sequence_value (tablename = 'test',
column_name = 'test_no',
increment = 1000);
low = high - 999;
commit;
while low <= high do
... statements ...
low = low + 1;
endwhile;