Was this helpful?
Sequence Expressions
Sequence expressions return values from defined database sequences.
NEXT VALUE FOR, CURRENT VALUE FOR
A sequence expression consists of one of two different operators: NEXT VALUE FOR or CURRENT VALUE FOR.
The NEXT VALUE operator returns the next available value from the referenced sequence:
NEXT VALUE FOR [schema.]sequence or [schema.]sequence.NEXTVAL
The CURRENT VALUE operator returns the previous value returned from the sequence to the executing application:
CURRENT VALUE FOR [schema.]sequence or [schema.]sequence.CURRVAL
Note:  The CURRENT VALUE operator cannot be used in a transaction until a NEXT VALUE operator is executed on the same sequence. This prevents transactions from seeing sequence values returned to other executing applications.
Sequence expressions are typically used in INSERT or UPDATE statements to maintain columns that reflect an ordinal relationship with the creation of their containing rows. For example:
INSERT INTO T1 VALUES (:hv1, NEXT VALUE FOR mydb.t1seq, ...)
or
INSERT INTO T2 SELECT col1, col2, t2seq.NEXTVAL, ...FROM ...
Sequence expressions can also be used in the select list of a SELECT statement. They cannot, however, be used in a WHERE, ON, GROUP BY, HAVING, ORDER BY, or UNION clause, in a subquery, or with aggregate functions.
A NEXT VALUE or CURRENT VALUE expression on a particular sequence is evaluated once per row inserted by an INSERT statement, updated by an UPDATE statement, or added to the result set of a SELECT statement. If several occurrences of a NEXT VALUE or CURRENT VALUE expression on the same sequence are coded in a single statement, only one value is computed for each row touched by the statement. If a NEXT VALUE expression and a CURRENT VALUE expression are coded on the same sequence in the same statement, the NEXT VALUE expression is evaluated first, then the CURRENT VALUE expression (assuring they return the same value), regardless of their order in the statement syntax.
LAST_IDENTITY Function
LAST_IDENTITY
Operand type: None
Result type: INTEGER8
Returns the last value generated for an identity column (see Identity Columns on page 491) in any table for the current session. If no identity value has been generated in the current session, returns NULL.
The number returned is specific to the current session, but not to a particular table.
Note:  The most recent identity number for this session may not necessarily be the highest number generated for any given table globally; another session may later generate a new number that will not affect the last number for the current session.
Note:  LAST_IDENTITY is not supported in Ingres Star. LAST_IDENTITY values can be obtained through DIRECT EXECUTE IMMEDIATE or DIRECT CONNECT, but values are relevant to the local session only, and do not represent the global last identity in the distributed session.
Locking and Sequences
In applications, sequences use logical locks that allow multiple transactions to retrieve and update the sequence value while preventing changes to the underlying sequence definition. The logical lock is held until the end of the transaction.
Last modified date: 01/30/2023