MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB

Grouped Customers Related to Grouped Orders with Aggregation

By nesting SHAPE commands, you can use the Data Shaping Service for OLE DB to create hierarchies with as many levels as you want and to aggregate child rows into parent rows. The grouped aggregation example uses three SHAPE commands to create a four-level hierarchy and uses the SUM function to aggregate child rows.

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 clause COMPUTE rsOrdDetail BY cust_id, month defines its parent, and the SUM function aggregates the child rowsets into totalAmtPerMonth.

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 phrase SUM(rsOrdByMonth.totalAmtPerMonth) AS totalAmtPerCustomer aggregates the previous total, totalAmtPerMonth, and sums it by rsOrdbyMonth for all orders selected into totalAmtPerCustomer.

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, and totalAmtPerState then holds the total figure of the first two aggregations.

SHAPE
   (SHAPE {select * from Customers} 
      APPEND SUM(rsOrdByMonth.totalAmtPerMonth) 
         AS totalAmtPerCustomer, 
         ((SHAPE {select * from Orders} 
               AS rsOrdDetail 
                  COMPUTE SUM(rsOrdDetail.order_amt) 
                     AS totalAmtPerMonth, rsOrdDetail
                  BY cust_id, month)  
         RELATE cust_id TO cust_id) 
            AS rsOrdByMonth ) 
      AS rsCustDetail 
   COMPUTE SUM(rsCustDetail.totalAmtPerCust) 
      AS totalAmtPerState, rsCustDetail
   BY state