MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB
By nesting SHAPE commands, you can use the Data Shaping Service for OLE DB to create hierarchies with as many levels as you want. The following example uses three SHAPE commands to create a four-level hierarchy.
In the following code example, data is retrieved from the Customers and Orders tables in the Sales database. The innermost SHAPE command uses select * from orders to create the lowest rowset in the hierarchy. The COMPUTE rsOrdDetail BY cust_id, month clause defines its parent.
The middle SHAPE command uses select * from Customers to create a parent rowset whose child is the parent rowset in the inner SHAPE command.
The outermost SHAPE command creates the root rowset in the hierarchy from
the parent rowset in the middle SHAPE command. It is created by partitioning
the Customers rowset by state.
SHAPE
(SHAPE {select * from Customers}
APPEND (
(SHAPE {select * from Orders}
AS rsOrdDetail
COMPUTE rsOrdDetail
BY cust_id, month)
RELATE cust_id TO cust_id)
AS rsOrdByMonth )
AS rsCustDetail
COMPUTE rsCustDetail
BY state
This results in a nested four-level hierarchy: