7.18.1 Specification format and rules for row value constructors

(1) Specification format

row-value-constructor::= (row-value-constructor-element[,row-value-constructor-element]…)
 
  row-value-constructor-element::= value-expression

To Page Top

(2) Explanation of specification format

row-value-constructor-element:

For a row value constructor, specify one or more row value constructor elements in the format of a value expression. For details about value expressions, see 7.21 Value expression.

The following are examples of row value constructors.

To Page Top

(3) Rules

1. A maximum of 4,000 row value constructor elements can be specified in the row value constructors.

To Page Top

(4) Examples

Example 1: Example of specifying a row value constructor for a table value constructor

Derive and retrieve a sales history table (SALESLIST) from the table value constructor.

SELECT "USERID","PUR-CODE","PUR-NUM" 
  FROM (VALUES('U001','P001',5)
             ,('U001','P002',3)
             ,('U002','P001',1)
       ) "SALESLIST"("USERID","PUR-CODE","PUR-NUM")

The underlined portion is the specification of row value constructors.

Execution result example

Example 2: Example of specifying a row value constructor for a comparison predicate

Retrieve the customer ID (USERID) U001 and product code (PUR-CODE) P001 from the sales history table (SALESLIST).

SELECT "USERID","PUR-CODE","PUR-NUM"
  FROM "SALESLIST"
  WHERE ("USERID","PUR-CODE") = ('U001','P001')

The underlined portion is the specification of row value constructors.

When the SELECT statement above is executed, the search result is as follows.

Example 3: Example of specifying a row value constructor for a IN predicate

Rows that match the following conditions are retrieved from the sales history table (SALESLIST).

• Customer ID (USERID) is U001 and product code (PUR-CODE) is P001

• Customer ID (USERID) is U002 and product code (PUR-CODE) is P001

SELECT "USERID","PUR-CODE","PUR-NUM"
  FROM "SALESLIST"
  WHERE ("USERID","PUR-CODE") IN (('U001','P001'), ('U002','P001'))

The underlined portion is the specification of row value constructors.

When the SELECT statement above is executed, the search result is as follows.

To Page Top