2.2.2 Query expression format 2 (unnesting query expression for repetition columns)

Organization of this subsection
(1) Function
(2) Format
(3) Operands
(4) Rules
(5) Example

(1) Function

This facility allows you to split a given repetition column by element and retrieve each element as a separate row.

In addition, this facility, which is called the unnesting facility for repetition columns, can retrieve elements in a repetition column having the same subscript as the same row.

(2) Format

 SELECT [ALL | DISTINCT] {selection-expression
               [,selection-expression] ...| * }
 FROM [authorization-identifier.] table-identifier
               [IN (RDAREA-name-specification)]
      (FLAT (column-name [,column-name] ...))
     [[AS] correlation-name]
      [used-index-SQL-optimization-specification]
      [WHERE search-condition]
      [GROUP BY column-specification [,column-specification]...]
      [HAVING search-condition]

(3) Operands

For details about selection expressions, FLAT, and operands other than search conditions, see 2.2.1 Query expression format 1 (general-query-expression).

Specifies the item to be output as a search result.

For selection expressions, see 2.3 Query specification.

IN
Specifies the RDAREA to be accessed. This operand cannot be specified in the following cases:
  • If the table specified in the table identifier is a read-only view table
  • While in a derived query expression of a view definition
RDAREA-name-specification::=value-specification
Use a value specification of the VARCHAR type, CHAR type, MVARCHAR type, or MCHAR type to specify the RDAREA name to be used as the RDAREA for storing the table specified in the table identifier. To specify multiple RDAREA names, separate them with commas (,). The same name cannot be used to specify another RDAREA name; doing so results in an error. For details about the characters allowed in an RDAREA name specified using a value specification, see 1.1.7 Specification of names. Spaces before or after an RDAREA name specified using a value specification are ignored. However, if the RDAREA name is enclosed in double quotation marks ("), only the spaces outside the double quotation marks are ignored.
When specifying an RDAREA while using the inner replica facility, specify the original RDAREA name. To target a replica RDAREA, use the change current database command (pddbchg command) or PDDBACCS operand of the client environment definition to change the RDAREA to be accessed to the replica RDAREA.

Specifies the repetition column or the list of columns containing repetition columns that are the object of flattening.

If multiple repetition columns are specified, the flattening is performed so that elements of the same subscript are contained in the same row.

If the specified repetition column also contains a normal column (a non-repetition structure column), the system generates rows having the same value with respect to any element in the repetition column.

After being flattened, any repetition column or a set of columns containing a repetition column becomes a normal column. Therefore, the columns must be specified as a normal column in that query expression.

In search-condition, you can specify items that have been flattened.

For search conditions, see 2.7 Search conditions.

(4) Rules

  1. The following table describes the relationship between columns to be flattened and the index.
    Relationship between a column specified in a column name and an indexSQL statement executable?
    Index definition providedHas an index that contains a repetition column.There is an index that covers all the columns specified in the SQL statement among the columns specified in a FLAT column name.The FLAT column name contains at least one repetition column.Y
    The FLAT column name does not contain any repetition column.N
    There is no index that covers all columns specified in the SQL statement, among the columns that are specified in a FLAT column name.N
    No index that contains a repetition columnN
    No index definitionN
    Legend: Y: executable, N: error
  2. If WITHOUT INDEX is specified in the SQL optimization specification for the index being used, the system ignores the optimization specification and performs retrievals using the index that is available to HiRDB.
  3. When specifying WITH INDEX in the SQL optimization specification for the index being used, specify an index that satisfies the SQL statement execution-enabling condition given in Rule 1. If the specified index does not satisfy the SQL statement execution-enabling condition, the system ignores the optimization specification and performs retrievals using the index that is available to HiRDB.
  4. In the FROM clause, a FLAT specification cannot be specified in the SELECT statement of INSERT SELECT, a WITH clause query, a view definition, a derived table in the FROM clause, or a subquery.
  5. The maximum number of column names that can be specified in FLAT is 16.
  6. FLAT must be used to specify column names in an SQL statement that specifies flattening.
  7. In table-identifier, a view table cannot be specified.
  8. The following items cannot be specified with flattening:
    • FOR UPDATE clause
    • FOR READ ONLY
    • LIMIT clause
    • Component specifications
    • Function calls
    • Subqueries
    • Set functions with a FLAT specification
    • Subscripted columns
  9. The following items cannot be specified in a search condition with flattening:
    • Structured repetition predicates
  10. If you specify the RDAREA name specification, you cannot use an index that has a different number of partitions than the table has. When defining an index for a query that specified an RDAREA name, define an index with the same number of partitions as the table has.

(5) Example

Flatten a table of test scores and retrieve a list of students who scored 70 points or higher.

SELECT name, subject, score, FROM test-score-table (FLAT (name, subject, score))
   WHERE score >=70

[Figure]
The following table and index definition apply to the test score table:

Table definition:
   CREATE TABLE test-score-table (name,MCHAR(10),
                       subject MCHAR(10) ARRAY[4],
                       score SMALLINT ARRAY[4]);
Index definition:
   CREATE INDEX subject score ON test-score-table (name, subject, score);