You can use hierarchical recordsets as an alternative to JOIN and GROUP BY syntax when you need to access parent-child and summary data. Hierarchical recordsets are used in many products: Xbase products use the SET RELATION command, Microsoft Access uses segmented virtual tables internally for reports with grouping levels, and so forth.
Hierarchical recordsets are available through the MSDataShape provider, which the client cursor engine implements. Hierarchies enable you to build one or several recordsets, define groupings, and specify aggregate calculations over child recordsets. Although you can implement similar functionality through code, this functionality shifts much of the mundane work from the developer to the system.
Difference Between Hierarchical Recordsets and SQL JOIN and GROUP BY Statements
Hierarchical recordsets differ from SQL JOIN and GROUP BY statements. In a SQL JOIN statement, both the parent table fields and child table fields are represented in the same recordset. In a hierarchical recordset, the recordset contains only fields from the parent table. In addition, the recordset contains an extra field that represents the related child data, which you can assign to a second recordset variable and traverse.When use GROUP BY and aggregate operators to perform aggregate functions, only aggregate values appear in the recordset. In hierarchical recordsets, the aggregate values are represented in the parent recordset, and the detail records are represented in the child recordset.
Different Types of Shapes
You can create three types of shapes:- Relation-based
- Parameter-based
- Group-based
Relation-Based Hierarchy
Both the relation-based and parameter-based hierarchies produce a hierarchy that is otherwise represented by a SQL JOIN statement. However, these hierarchies differ in how they read the parent and child records. In the relation-based hierarchy, all of the parent and child records are read into a local cache before any processing continues. As a result, the relation-based hierarchy has a high initial overhead when you retrieve the records. However, the overhead is low after the initial retrieval.Parameter-Based Hierarchy
Initially, parameter-based hierarchies only read the parent records and fetch the child records on demand. Although the initial overhead is reduced, you must issue a new child query for each parent record that you access, and you must maintain the connection to the data source for as long as the recordset is open.Group-Based Hierarchy
The group-based hierarchy is equivalent to producing an aggregate SQL statement that is joined to a detail SQL statement. The group-based hierarchy is also equivalent to performing aggregate functions on non-normalized data. You cannot update the summary columns and calculated columns because they may be derived from more than one record. Like relation-based hierarchies, all records must be read up front.Simplified Syntax
The SHAPE clause makes the hierarchical recordsets available. This section provides the simplified syntax. Because the SHAPE syntax can become quite complex, the formal grammar for the SHAPE clause is provided at the end of the article to allow you to extend the examples. You can also use the program at the end of this article to test your own SHAPE statements.
SHAPE {parent-statement}
APPEND Aggregate
| ({child-statement} [As Alias]
RELATE parent-field TO child-field | parameter-marker
[, parent-field TO child-field | parameter-marker ...])
[, Aggregate | ({child statement})...]
SHAPE {non-normalized-statement} [AS Alias]
COMPUTE Aggregate
| Alias
| ({child-statement} [As Alias] RELATE parent-field TO
child-field | parameter-marker)
[, Aggregate | Alias | ({child-statement}...)]
[BY grouping-field [, grouping-field]]
SHAPE {non-normalized-statement} [AS Alias]
BY grouping-field [, grouping-field]
Notes
- If you select fields that have identical names from different tables, you may need to assign them an alias to ensure that the SHAPE parser works.
- The SHAPE APPEND syntax functions similarly to an OUTER JOIN statement; a parent record is returned even if no child records exist for it.
- Aggregates can only operate on fields in the immediate children of the recordset. To operate on fields in grandchildren and so forth, you must produce intermediate aggregates. See the Group Hierarchy with Aggregate example for an illustration.
- If you use an aggregate function with the SHAPE APPEND syntax, the aggregate value occupies a field that is appended to the parent resultset, which also contains the fields from the parent statement. In contrast, SHAPE COMPUTE and SHAPE BY create a new parent level for the aggregates, and the non-normalized statement becomes the child recordset.
- The SHAPE provider requires that you include Alias for the non-normalized statement in the COMPUTE clause when you use SHAPE COMPUTE. If you do not, you receive an error message that the functionality is not supported, even though you do not receive a syntax error.
Examples
This section provides examples with diagrams. These examples use tables from the Northwind sample database.Simple Relation Hierarchy
SHAPE {select * from customers}
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid)
Customers.*
rsOrders
|
+----Orders.*
Parameterized Hierarchy
SHAPE {select * from customers}
APPEND ({select * from orders where customerid = ?} AS rsOrders
RELATE customerid TO PARAMETER 0)
Compound Relation Hierarchy
This sample illustrates a three-level hierarchy of customers, orders, and order details:
SHAPE {SELECT * from customers}
APPEND ((SHAPE {select * from orders}
APPEND ({select * from [order details]} AS rsDetails
RELATE orderid TO orderid)) AS rsOrders
RELATE customerid TO customerid)
Customers.*
rsOrders
|
+----Orders.*
rsDetails
|
+----[Order Details].*
Multiple Relation Hierarchy
This sample illustrates a hierarchy that involves a parent recordset and two child recordsets, one of which is parameterized:
SHAPE {SELECT * FROM customers}
APPEND ({SELECT *
FROM orders
WHERE orderdate < #1/1/1998# AND customerid = ?}
RELATE customerid TO PARAMETER 0) AS rsOldOrders,
({SELECT *
FROM orders
WHERE orderdate >= #1/1/1998#}
RELATE customerid TO customerid) AS rsRecentOrders
Customers.*
rsOldOrders
|
+----Orders.*
rsRecentOrders
|
+----Orders.*
Hierarchy with Aggregate
SHAPE (select * from orders}
APPEND ({select od.orderid, od.UnitPrice * od.quantity as ExtendedPrice
from [order details] As od}
RELATE orderid TO orderid) As rsDetails,
SUM(ExtendedPrice) AS OrderTotal
Orders.*
rsDetails
|
+----orderid
ExtendedPrice
OrderTotal
Group Hierarchy
SHAPE {select customers.customerid AS cust_id, orders.*
from customers inner join orders
on customers.customerid = orders.customerid} AS rsOrders
COMPUTE rsOrders BY cust_id
rsOrders
|
+----cust_id
Orders.*
cust_id
Group Hierarchy with Aggregate
NOTE: The inner SHAPE clause in this example is identical to the statement that is used in the Hierarchy with Aggregate example.
SHAPE
(SHAPE {select customers.*, orders.orderid, orders.orderdate
from customers inner join orders
on customers.customerid = orders.customerid}
APPEND ({select od.orderid,
od.unitprice * od.quantity as ExtendedPrice
from [order details] as od} AS rsDetails
RELATE orderid TO orderid),
SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS rsOrders
COMPUTE rsOrders,
SUM(rsOrders.OrderTotal) AS CustTotal,
ANY(rsOrders.contactname) AS Contact
BY customerid
rsOrders
|
+----Customers.*
orderid
orderdate
rsDetails
|
+----orderid
ExtendedPrice
OrderTotal
CustomerTotal
Contact
customerid
Multiple Groupings
SHAPE
(SHAPE {select customers.*,
od.unitprice * od.quantity as ExtendedPrice
from (customers inner join orders
on customers.customerid = orders.customerid) inner join
[order details] as od on orders.orderid = od.orderid}
AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
ANY(rsDetail.region) AS Region,
SUM(rsDetail.ExtendedPrice) AS CustTotal,
rsDetail
BY customerid) AS rsCustSummary
COMPUTE rsCustSummary
BY Region
rsCustSummary
|
+-----Contact
Region
CustTotal
rsDetail
|
+----Customers.*
ExtendedPrice
customerid
Region
Grand Total
SHAPE
(SHAPE {select customers.*,
od.unitprice * od.quantity as ExtendedPrice
from (customers inner join orders
on customers.customerid = orders.customerid) inner join
[order details] as od on orders.orderid = od.orderid}
AS rsDetail
COMPUTE ANY(rsDetail.contactname) AS Contact,
SUM(rsDetail.ExtendedPrice) AS CustTotal,
rsDetail
BY customerid) AS rsCustSummary
COMPUTE SUM(rsCustSummary.CustTotal) As GrandTotal,
rsCustSummary
GrandTotal
rsCustSummary
|
+-----Contact
CustTotal
rsDetail
|
+----Customers.*
ExtendedPrice
customerid
Complex Hierarchy
This example illustrates a hierarchy that contains one parent rowset, two child rowsets (one of which is parameterized), and a group detail.
SHAPE {select customers.* from customers} AS rsDetail
COMPUTE rsDetail,
ANY(rsDetail.companyname) AS Company,
({select * from orders where customerid = ?}
RELATE customerid TO PARAMETER 0) AS rsOrders,
COUNT(rsOrders.orderid) AS OrderCount
BY customerid
rsDetail
|
+----Customers.*
Company
rsOrders
|
+----Orders.*
OrderCount
customerid
Grouped Parent Related to Grouped Child
SHAPE
(SHAPE {select * from customers}
APPEND ((SHAPE {select orders.*, year(orderdate) as OrderYear,
month(orderdate) as OrderMonth
from orders} AS rsOrders
COMPUTE rsOrders
BY customerid, OrderYear, OrderMonth)
RELATE customerid TO customerid) AS rsOrdByMonth )
AS rsCustomers
COMPUTE rsCustomers
BY region
rsCustomers
|
+-----customers.*
rsOrdByMonth
|
+-----rsOrders
|
+---- Orders.*
customerid
OrderYear
OrderMonth
region
SHAPE Clause Formal Grammar
<shape-command> ::= SHAPE <table-exp> [AS <alias>]
[<shape_action>]
<shape-action> ::= APPEND <aliased-field-list>
| COMPUTE <aliased-field-list>
[BY <field-list>]
| BY <field-list>
<table-exp> ::= {<native-sql-statement>}
| ( <shape-command> )
<aliased-field-list> ::= <aliased-field> [, <aliased-field...]
<aliased-field> ::= <field-exp> [AS <alias>]
<field-exp> ::= ( <relation-exp> ) | <calculated-exp>
<relation_exp> ::= <table-exp> [AS <alias>] RELATE
<relation-cond-list>
<relation-cond-list> ::= <relation-cond> [, <relation-cond>...]
<relation-cond> ::= <field-name> TO <child-ref>
<child-ref> ::= <field-name> | PARAMETER <param-ref>
<param-ref> ::= <name> | <number>
<field-list> ::= <field-name [, <filed-name>]
<calculated-exp> ::= SUM (<qualified-field-name>)
| AVG (<qualified-field-name>)
| MIN (<qualified-field-name>)
| MAX (<qualified-field-name>)
| COUNT (<alias>)
| SDEV (<qualified-field-name>)
| ANY (<qualified-field-name>)
| CALC (<expression>)
<qualified-field-name>::= <alias>.<field-name> | <field-name>
<alias> ::= <quoted-name>
<field-name> ::= <quoted-name>
<quoted-name> ::= "<string>" | '<string>' | <name>
<name> ::= alpha [ alpha | digit | _ | # ...]
<number> ::= digit [digit...]
<string> ::= unicode-char [unicode-char...]
<expression> ::= an expression recognized by the Jet
Expression service whose operands are
other non-CALC columns in the same row.
Visual Basic .NET SHAPE Test Program
The following Visual Basic .NET code enables you to type your own SHAPE command and display the field hierarchy or indicate the location of the syntax error. This sample uses the ADO.NET DataReader object to retrieve the hierarchical data.Important: You must use the OLE DB managed provider with the MSDataShape provider. You cannot use the SQL or ODBC managed providers.
- Create a new Windows Application project in Visual Basic .NET.
- Add two TextBox controls and one Button control. TextBox1, TextBox2, and Button1 are added by default.
- Set the following properties in TextBox1 and TextBox2:Multiline: True
Scrollbars: Vertical - Enlarge both text boxes so that they are large enough to display several lines of text.
- Add the following code to the top of the default form's
code module:
Imports System.Data.OleDb
- Click to expand the Windows Form Designer generated
code region, and add the following code after the InitializeComponent call to place a default SHAPE statement in TextBox1. After you copy the code, hide the Windows Form Designer region.
Me.TextBox1.Text = "SHAPE {SELECT * FROM CUSTOMERS} APPEND ({SELECT * FROM ORDERS} " & _ "AS CustOrders RELATE CustomerID TO CustomerID)" - Add the following code under the Windows Form
Designer generated code region.
Note You must change User ID <username> and password =<strong password> to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database.Dim cn As OleDbConnection Dim cmd As OleDbCommand Dim drCust As OleDbDataReader Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _ Handles Button1.Click cn = New OleDbConnection("Provider=MSDataShape;Data Provider=SQLOLEDB;" & _ "Data Source=(local);Initial Catalog=Northwind;User ID=<username>;Password=<strong password>") cn.Open() cmd = New OleDbCommand(TextBox1.Text, cn) drCust = cmd.ExecuteReader() TextBox2.Clear() ListChapteredFields(drCust, 0) End Sub Private Sub ListChapteredFields(ByVal dr As OleDbDataReader, ByVal lngLevel As Long) Dim i As Integer Dim drOrders As OleDbDataReader dr.Read() For i = 0 To dr.FieldCount - 1 LogText(Space(lngLevel * 3) & dr.GetName(i) & vbTab) 'Looking for FieldType of System.Data.IDataReader If TypeOf dr(i) Is IDataReader Then drOrders = dr.GetValue(i) ListChapteredFields(drOrders, lngLevel + 1) End If Next End Sub Private Sub LogText(ByVal strLine As String) If TextBox2.Text = "" Then TextBox2.Text = strLine Else TextBox2.Text = TextBox2.Text & vbCrLf & strLine End If End Sub - Make sure that you update the OleDbConnection object's connection string to use your server, user name, and password.
- Run the project, and click the command button. Notice that the hierarchy of fields appears in TextBox2.
- Type (or copy and paste) a different SHAPE command in TextBox1, and click the command button. Notice that the hierarchy of fields appears in TextBox2.