Kbase P33318: How to retrieve the next value from a sequence in SQL-92 and get only one result?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
22/02/2005 |
|
Status: Verified
GOAL:
How to retrieve the next value from a sequence in SQL-92 and get only one result?
FACT(s) (Environment):
Progress 9.1D
OpenEdge 10.x
FIX:
The normal way to retrieve the next value from a sequence is with the following code:
SELECT PUB.YourSequenceNameGoesHere.NEXTVAL FROM PUB.SomeTableName
However, if the table (PUB.SomeTableName) contains more than one record you will get a result set containing multiple records and consequently the sequence will be incremented once for each record returned.
The ways to resolve this are as follows:
- Use a WHERE clause that will limit the result set to one record
- Create a table that contains a single field and only one record (i.e. basically a dummy table) and always use that table as the table that your SELECT statement works against. This will guarantee that only one record will be returned and the sequence will only be incremented once.