NEXTVAL

NEXTVAL returns a sequence's next value. References to NEXTVAL increment the sequence value by the defined increment and return the new value.

Use the following syntax to reference the next value of a sequence:

Syntax

schema.sequence.NEXTVAL

Parameters

schema

Specifies the schema that contains the sequence. To refer to the next value of a sequence in the schema of another user, you must have SELECT object privilege on the sequence.

sequence

Specifies the name of the sequence whose next value you want.A statement referencing NEXTVAL for a noncycling sequence returns an error after reaching the maximum value.

Use NEXTVAL in the:

SELECT list of a SELECT statement not contained in a subquery or view

SELECT list of a subquery in an INSERT statement

VALUES clause of an INSERT statement

SET clause of an UPDATE statement

NEXTVAL cannot be used in:

A query of a view

A SELECT statement with a GROUP BY clause that references a sequence

A SELECT statement with an ORDER BY clause that references a sequence

A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator and this restriction applies even if UNION, INTERSECT, or MINUS occur in a derived table clause

The WHERE clause of a SELECT or UPDATE statement

The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement

The condition of a CHECK constraint

Example

In the following example, the sequence generator increments the customer sequence and uses its value for a new customer inserted into the table pub.customer:

INSERT INTO pub.customer VALUES (customer_sequence.NEXTVAL,'USA','BackCountry Equipment','Sugar Hill Road','12A','Franconia','NH','03242','Dan Egan','603-762-2121','Kirsten Ulmner', 10000.00, 500.00,'net 10', 0,'contact monthly');