7.17.1 Specification format and rules for table value constructors

(1) Specification format

table-value-constructor ::= VALUES row-value-constructor[,row-value-constructor]...
 
  row-value-constructor ::= (row-value-constructor-element[,row-value-constructor-element]...)
    row-value-constructor-element ::= {value-specification | scalar-function-CAST | scalar-function-CONVERT}

To Page Top

(2) Explanation of specification format

row-value-constructor:

row-value-constructor ::= (row-value-constructor-element[,row-value-constructor-element]...)
  row-value-constructor-element ::= {value-specification | scalar-function-CAST | scalar-function-CONVERT}

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.

Examples:

value-specification:

Specify row value constructor elements in the form of a value specification. For details about value specifications, see 7.21 Value specification.

scalar-function-CAST:

Specify row value constructor elements by using the scalar function CAST. For details about the scalar function CAST, see 8.12.3 CAST.

The following rules apply:

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

scalar-function-CONVERT:

Specify row value constructor elements by using the scalar function CONVERT. For details about the scalar function CONVERT, see 8.12.5 CONVERT.

The following 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.20.2 Data types of the results of value expressions.

4. A maximum of 30,000 row value constructors can be specified.

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

6. A maximum of 1,000 row value constructor elements can be specified in each row value constructor.

7. The dynamic parameter cannot be specified by itself as a row value constructor element.

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