Generating Hierarchical Results

A hierarchical query nests a SELECT statement as a column of the rowset retrieved by a nested SELECT statement.

Use braces ({}) to delimit the nesting.

Accessing Hierarchical Data Using SQL

Data stored hierarchically in a VSAM data source can be referenced by using a hyphen followed by a right arrow (->) to denote the parent child relationship in the source:

FROM … parent_name->child1->child2… [alias]

Or, using an alias for the parent table:

FROM … parent_alias->child1->child2… [alias]

Flattening Hierarchical Data Using SQL

You can produce a flattened view of hierarchical data by embedding a SELECT statement inside the list of columns to be retrieved by another SELECT statement. You use parentheses to delimit the nesting. The nested SELECT statement can reference a child rowset (using the parent->child syntax) only in its FROM clause.

Using an Alias

To list the hierarchical data with the parent data only, you must use an alias for the child data.

Note:

Without an alias the query lists, for each parent row, all of the children of all of the parent rows.

Scripting on this page enhances content navigation, but does not change the content in any way.