Kbase 15265: How to get NEXT-VALUE of a SEQUENCE from E/SQL
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/10/1998 |
|
How to get NEXT-VALUE of a SEQUENCE from E/SQL
This notebook entry discusses how the NEXT-VALUE function can be used
to get the next value of a SEQUENCE in a database (V7 and later).
The syntax required is:
SELECT NEXT\-VALUE(sequence_name) FROM <table>
or:
SELECT NEXT\-VALUE(sequence_name) INTO :sequence
FROM <table> WHERE <where clause>
NOTE: The "-" in the NEXT-VALUE keyword must be "escaped" or you can
specify the -esqlnopad startup parameter.
CAUTION: If there is more than one record in the source table (the
table referenced by the FROM keyword), you will get as many values
from the sequence as there are records in the source table. Likewise,
the WHERE clause in the SELECT ... INTO query is important: a SELECT
... INTO query will return error if more than one row satisfies the
WHERE condition, but since seuqneces are managed separately from the
E/SQL transaction context, the NEXT-VALUE operation can't be rolled
back if the query errors out. If you need to do this frequently, you
might want to set up a frozen table that has only one row so that
you needn't be so careful about the WHERE clause, or use a cursor and
only FETCH it once.
Progress Software Technical Support Note # 15265