MDAC 2.5 SDK - OLE DB Providers
Data Shaping Service for OLE DB
The Data Shaping Service for OLE DB can be used to group any number of times within a hierarchical structure to produce totals at different levels. In this example, the summary of the first group, the Customer Group (rsCustGrp), is calculated by computing all customer details, and then the Customer Group is summmed by state, which forms the second grouping. Two SHAPE commands are used here: One builds the Customer Group hierarchy; the other builds the State hierarchy using the results of the Customer Group hierarchy.
Shaping the Customer Group Hierarchy
In this example, certain columns are selected by cust_id from the Customers table, including the order amount, and the order information is selected from the Orders table. All data is fetched before it is related and summed. The resulting child rowset is named rsDetail. RsDetail is then summed using the ordered amount for any customer ID within the scope. The resulting rowset is called rsCustGrp and is the parent of rsDetail. It contains the summary information of all child rowsets. When accessing a parent row, the internally created index relates the parent to its child rowsets.
Shaping the State Hierarchy
After the Customer group has been built into a relational hierarchy, with parent rows associated with their child rows through the index, the State group is built. The Customer group is the child rowset associated with its parent the State row. Again, the relation is made by creating the index or filter.
SHAPE
(SHAPE {select customer.*, orders.order_id, name,
orders.order_amt from Customers, Orders
where customer.cust_id = orders.cust_id}
AS rsDetail
COMPUTE ANY(rsDetail.cust_name)
AS cust_name,
ANY(rsDetail.state)
AS state,
SUM(rsDetail.order_amt)
AS ord_amt, rsDetail
BY cust_id )
AS rsCustGrp
COMPUTE rsCustGrp
BY state
This results in the following aggregations within this multiple grouped hierarchy: