NEXT VALUE FOR Expression
NEXT VALUE FOR expression retrieves the next value from a sequence
generator that was created with a
NEXT VALUE FOR sequenceName
A sequence name is an identifier that can optionally be qualified by a schema name:
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 the
If this is the first use of the sequence generator, the generator
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:
- If the sequence generator was created using the
CYCLEkeyword, the sequence generator is reset to its
- If the sequence generator was created with the default
NO CYCLEbehavior, Splice Machine throws an exception.
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.
NEXT VALUE FOR expression may occur in the following places:
SELECTstatement: As part of the expression defining a returned column in a
VALUESexpression: As part of the expression defining a column in a row constructor (
UPDATEstatement; As part of the expression defining the new value to which a column is being set
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.
NEXT VALUE FOR expression is allowed per sequence per
NEXT VALUE FOR expression is not allowed in any statement which
ORDER BY expression.
NEXT VALUE expression may not appear in any of these situations:
- Aggregate expression
- Window functions
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;