MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB
The Data Shaping Service for OLE DB can be used to sum up a hierarchical structure to produce totals at different levels. In this example, certain columns are selected by cust_id from the Customers table, including the order amount; the order information is selected from the Orders table. All data is fetched before it is related and summed. In this example, the resulting child rowset is named rsDetail. RsDetail is summed using the ordered amount for any customer ID within the scope. The resulting Recordset is the parent of rsDetail and contains the summary information of all child rowsets. When accessing a parent row, the internally created index relates the parent to its child rowsets.
SHAPE {select customer.*, orders.order_id, orders.order_amt
from customers, orders where customer.cust_id = orders.cust_id}
AS rsDetail
COMPUTE rsDetail, SUM(rsDetail.order_amt)
AS order_amt
COUNT(rsDetail)
AS DetailCount
BY cust_id
This results in the following aggregations within this hierarchy: