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.
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]
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.
Relationship between a column specified in a column name and an index | SQL statement executable? | |||
---|---|---|---|---|
Index definition provided | Has 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 column | N | |||
No index definition | N |
SELECT name, subject, score, FROM test-score-table (FLAT (name, subject, score))
WHERE score >=70
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);