NEXT VALUE FOR expression

The NEXT VALUE FOR expression retrieves the next value from a sequence generator that was created with a CREATE SEQUENCE statement.

Syntax

NEXT VALUE FOR sequenceName

If this is the first use of the sequence generator, the generator returns its START value. Otherwise, the INCREMENT value is added to the previous value returned by the sequence generator. The data type of the value is the dataType specified for the sequence generator.

If the sequence generator wraps around, then one of the following happens:

In order to retrieve the next value of a sequence generator, you or your session's current role must have USAGE privilege on the generator.

A NEXT VALUE FOR expression may occur in the following places:

Only one NEXT VALUE FOR expression is allowed per sequence per statement.

The NEXT VALUE FOR expression is not allowed in any statement which has a DISTINCT or ORDER BY expression.

The next value of a sequence generator is not affected by whether the user commits or rolls back a transaction which invoked the sequence generator.

A NEXT VALUE expression may not appear in any of these situations:

sequenceName

[ schemaName. ] SQL92Identifier

If schemaName is not provided, the current schema is the default schema. If a qualified sequence name is specified, the schema name cannot begin with SYS.

Examples

VALUES (NEXT VALUE FOR order_id);
INSERT INTO re_order_table
  SELECT NEXT VALUE FOR order_id, order_date, quantity
  FROM orders
  WHERE back_order = 1;
UPDATE orders
  SET oid = NEXT VALUE FOR order_id
  WHERE expired = 1;
Related concepts
Dynamic parameters
Related reference
SelectExpression
TableExpression
VALUES expression
Expression precedence
Boolean expressions
CASE expression