NEXTVAL
Returns the next value in a sequence. Call NEXTVAL
after creating a sequence to initialize the sequence with its default value. Thereafter, call NEXTVAL
to increment the sequence value for ascending sequences, or decrement its value for descending sequences.
Behavior Type
Syntax
NEXTVAL('[[database.]schema.]sequence')
Parameters
[database.]schema
|
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. |
sequence |
Identifies the target sequence. |
Privileges
- SELECT privilege on sequence
- USAGE privilege on sequence schema
Restrictions
You cannot invoke NEXTVAL
in a SELECT
statement, in the following contexts:
WHERE
clauseGROUP BY
clauseORDER BY
clauseDISTINCT
clauseUNION
- Subquery
You also cannot invoke NEXTVAL to act on a sequence in:
- UPDATE or DELETE subqueries
- Views
You can use subqueries to work around some of these restrictions. For example, to use sequences with a DISTINCT
clause:
=> SELECT t.col1, shift_allocation_seq.NEXTVAL FROM ( SELECT DISTINCT col1 FROM av_temp1) t;
Examples
See Creating and Using Named Sequences in the Administrator's Guide