Grouped Customers Related to Grouped Orders

Important

This feature will be removed in a future version of Windows. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, applications should use XML.

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: