SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Language Elements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004
6-106
Considerations for Pseudocolumns
Considerations for Pseudocolumns
You must access NEXTVAL at least once in a session before accessing CURRVAL.
You can access NEXTVAL and CURRVAL in same or different statements in a session.
You can use the pseudocolumns in:
•
The select list of a SELECT statement that is not contained in the subquery, or
the view
•
The select list of a top level subquery in an INSERT statement
•
The VALUES clause of an INSERT statement
•
The SET clause of an UPDATE statement
You must not use the pseudocolumns in:
•
A trigger, view, or constraint definition
•
An embedded UPDATE, DELETE, or stream access query
•
A DELETE statement
•
An aggregate or sequence function
•
The WHERE clause of a SELECT statement
•
A query with a GROUP BY, SEQUENCE BY, or ORDER BY clause
•
A CASE expression
•
A query with a UNION clause
•
As a parameter to a CALL statement
Rules for Pseudocolumns
•
After a sequence generator is created, the first reference to NEXTVAL from an
SQL statement returns the initial value (START WITH value) of the sequence.
Subsequent references to NEXTVAL returns the current value and then increments
or decrements the sequence value by the defined increment or decrement.
•
Any reference to CURRVAL always returns the current value of the sequence,
which is the value returned by the last reference to NEXTVAL.
•
If the SQL statement contains references to both CURRVAL and NEXTVAL, then
the sequence is incremented or decremented and the same value is returned for
both CURRVAL and NEXTVAL.
•
The NEXTVAL value is incremented or decremented for the following:
•
Every row inserted by the outermost INSERT statement.