User's Manual

SQL Enhancements
D-4 Oracle Application Server Adapters for IMS/DB User's Guide
Accessing Hierarchical Data Using SQL
Data stored hierarchically in a IMS/DB 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.