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 [[RD-node-name.]authorization-identifier.]
       table-identifier (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 operands other than selection-expression, FLAT, and search condition, 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.

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 should 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 shows 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, a foreign table, or a table alias 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

(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);