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

Grandchild Aggregations

The Data Shaping Service for OLE DB allows you to perform grandchild aggregations. Use this command structure when you want to compute the average of the detail records, not the average of the parent rows.

In the following example, the inner SHAPE command selects from the Orders and OrderItems tables to build a relationship where rsOrders becomes the parent of rsDetails. When the outer SHAPE command is executed, it creates AvgDetailAmt and performs the average function (AVG) upon the Amount column in the grandchild (rsDetails) across all the rows in the corresponding child (rsOrders) to get the average of all the details.

SHAPE {select * from Customers}
   APPEND 
((SHAPE {select * from Orders}
         APPEND({select * from OrderItems} 
         RELATE order_id to order_id) 
            AS rsDetails) RELATE cust_id to cust_id) 
      AS rsOrders,
   AVG(rsOrders.rsDetails.Amount) 
      AS AvgDetailAmt

This results in the following aggregations: