2.28 NEXT VALUE expression

Organization of this section
(1) Overview
(2) Privileges
(3) Format
(4) Explanation
(5) Syntax rules
(6) Common rules
(7) Notes
(8) Examples

(1) Overview

A NEXT VALUE expression returns the value generated by a sequence generator.

(2) Privileges

By specifying FOR PUBLIC USAGE when you define a NEXT VALUE expression, all users can access a sequence generator. Only the owner can access any other sequence generator.

(3) Format

NEXT-VALUE-expression::= NEXT VALUE FOR [authorization-identifier.]sequence-generator-identifier

(4) Explanation

(5) Syntax rules

  1. The data type of the result for a NEXT VALUE expression is the data type of the value generated by the specified sequence generator.
  2. A NEXT VALUE expression can be specified without specifying a subquery in the following locations:
    • Selection expression of a query specification in an INSERT statement
    • Insertion value in an INSERT statement
    • Update value in an UPDATE statement
    Specification is not possible in the following locations:
    • CASE expression
    • In a VALUE scalar function
    • Query specification specifying a GROUP BY clause, HAVING clause, or set function
    • Query specification specifying a window function
    • Query specification that includes DISTINCT
    • Query specification as an operand of a set operation other than UNION ALL
  3. The result of the NEXT VALUE expression is without the NOT NULL constraint (the null value is allowed).

(6) Common rules

  1. When the NEXT VALUE expression is executed the first time after defining a sequence generator, the start value is returned.
  2. When an insertion value is specified, the value generated by the specified sequence generator is inserted in each row specified in the INSERT statement.
  3. When an update value is specified, the value generated by the specified sequence generator is used to update each row specified in the UPDATE statement.
  4. If a NEXT VALUE expression is specified more than once for the same sequence generator for the same row, all of the NEXT VALUE expressions return the same value.

(7) Notes

  1. The value from a sequence generator will not return to its original value, even if a rollback occurs and the transaction is disabled.
  2. The value from a sequence generator may be updated even if the result of executing the SQL statement specifying a NEXT VALUE expression returns an error.

(8) Examples

  1. This example defines an inventory table (STOCK) that has a product ID (PID), product name (PNAME), and price per unit (PRICE).

    CREATE TABLE STOCK(
    PID INTEGER,
    PNAME NCHAR(8),
    PRICE INTEGER)

  2. This example defines an SEQ1 sequence generator that numbers product IDs from 1000 to 9999.

    CREATE SEQUENCE SEQ1
    START WITH 1000
    INCREMENT BY 1
    MAXVALUE 9999

    NO CYCLE
  3. This example registers new products into an inventory table (STOCK).

    INSERT INTO STOCK VALUES(NEXT VALUE FOR SEQ1, N'Pants', 1200)
    INSERT INTO STOCK VALUES(NEXT VALUE FOR SEQ1, N'Shirt', 1000)
    INSERT INTO STOCK VALUES(NEXT VALUE FOR SEQ1, N'Sweater', 1500)

    Execution result

    [Figure]

  4. This example updates a product ID (PID) of an inventory table (STOCK) from 1001 to a new product ID.

    UPDATE STOCK SET PID = NEXT VALUE FOR SEQ1 WHERE PID = 1001

    Execution result

    [Figure]