7.17.1 Specification format and rules for table value constructors

(1) Specification format

table-value-constructor::= VALUES row-value-constructor[,row-value-constructor]…

To Page Top

(2) Explanation of specification format

row-value-constructor:

For a row value constructor, specify one or more row value constructor elements. The value of each row value constructor element becomes the value of each column on a row of a derived table. For details about row value constructors, see 7.18 Row value constructors.

The following is an example table value constructors.

Examples:

You can specify any of the following as the row value constructor element of the row value constructor in the table value constructor.

• Value specification

  For details about value specifications, see 7.22 Value specification.

• Scalar function CAST

  For details about the scalar function CAST, see 8.13.3 CAST.

  When specifying the scalar function CAST, the following specification rules apply.

  • For the data to be converted, only NULL or a dynamic parameter can be specified.

• Scalar function CONVERT

  For details about the scalar function CONVERT, see 8.13.5 CONVERT.

  When specifying the scalar function CONVERT, the following specification rules apply.

  • For the data to be converted, only NULL or a dynamic parameter can be specified.

  • A format specification cannot be specified.

To Page Top

(3) Rules

1. Make sure that each row value constructor has the same number of row value constructor elements.

   Example of correct specification: VALUES (11,12,13),(21,22,23),(31,32,33)

   Example of incorrect specification: VALUES (11,12,13),(21,22),(31,32,33,34)

2. The i-th row value constructor elements of all row value constructors must have data types that can be compared mutually. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.

   Example of correct specification: VALUES (11,12,13),(21.1,22.2,23.3),(1.0E+1,1.0E+2,1.0E+3)

   Example of incorrect specification: VALUES (11,12,13),('AB','CD',23)

   Note, however, that the following items of data cannot be compared:

   • Date data and the predefined input representation of a date

   • Time data and the predefined input representation of a time

   • Time stamp data and the predefined input representation of a time stamp

3. The data type and length of the result for the i-th column derived by a table value constructor is determined by the data type of the i-th row value constructor element of each row value constructor. For details, see 7.21.2 Data types of the results of value expressions.

4. A maximum of 30,000 row value constructors can be specified in a table value constructor.

5. The maximum total number of table value constructors and query specifications in one SQL statement is 1,024.

6. A dynamic parameter alone cannot be specified in a row value constructor element in a table value constructor.

To Page Top

(4) Examples

Example 1

In this example, you run the SELECT statement with table value constructors specified.

SELECT "C1","C2","C3" FROM (VALUES (11,12,13),
                                   (21,22,23)
                           ) AS "V1"("C1","C2","C3")

The underlined portion is the specification of table value constructors.

Example of execution results

Example 2

In this example, you retrieve a list of customers (customer IDs and names) who have purchased a product whose product code (PUR-CODE) is P001 (excluding duplicates) from the sales history table (SALESLIST) and customer table (USERSLIST) derived by table value constructors.

SELECT DISTINCT "USERSLIST"."USERID","NAME"
  FROM "SALESLIST"
         INNER JOIN
         (VALUES('U001','Maria'),('U002','Nancy')) AS "USERSLIST"("USERID","NAME")
            ON "USERSLIST"."USERID"="SALESLIST"."USERID"
        WHERE "SALESLIST"."PUR-CODE"='P001'

The underlined portion is the specification of table value constructors.

Example 3

In this example, you insert multiple data items into the customer table (USERSLIST).

INSERT INTO "USERSLIST"("USERID","AGE")
    SELECT * FROM (VALUES('USER001',10),('USER002',20))

The underlined portion is the specification of table value constructors.

To Page Top