MORE INFORMATION
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, Access uses "Segmented Virtual Tables"
internally for reports with grouping levels, and so forth. Hierarchies give you
the ability to build one or several recordsets, define groupings, and specify
aggregate calculations over child recordsets. Although you could implement
similar functionality through code, this functionality shifts much of the
mundane work from the developer to the system.
Hierarchical
recordsets are available through the MSDataShape provider, which is implemented
by the client cursor engine.
Hierarchical recordsets differ from SQL
JOIN and
GROUP BY statements in that with a
JOIN, both the parent table fields and child table fields are
represented in the same recordset. With 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 you are performing
aggregate functions using
GROUP BY and aggregate operators, only aggregate values appear in the
recordset. With hierarchical recordsets, the aggregate values are represented
in the parent recordset and the detail records are in the child
recordset.
You can create three types of shapes, and each type has
its own strengths and weaknesses. You need to choose the mechanism that best
fits the needs of your application and the environment you will be running your
application in. The types of
SHAPE are as follows:
- Relation Based
- Parameter Based
- Group Based
The first two are similar in that they produce a hierarchy that
would otherwise be represented by a SQL
JOIN statement. They differ in that all the parent and child records
are read into a local cache before any processing continues in the
relation-based hierarchy. This type of hierarchy has a high initial overhead
when retrieving the records, but the overhead is low after the initial
retrieval.
Initially, parameter-based hierarchies only read the
parent records and fetch the child records on demand. Though the initial
overhead is reduced, you must issue a new child query for each parent record
that is accessed, and you must maintain the connection to the datasource for as
long as the recordset is open.
The group-based hierarchy is
equivalent to producing an aggregate SQL statement joined to a detail SQL
statement or performing aggregate functions on non-normalized data. You cannot
update the summary columns and calculated columns because they might be derived
from more than one record. Like relation-based hierarchies, all records must be
read up front.
Hierarchical recordsets are made available by the
SHAPE clause. Simplified syntax is provided first, then examples with
diagrams. Because the
SHAPE syntax can get 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. The examples use tables from the Northwind sample
database.
Simplified Syntax
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 identically named fields from different
tables, you might need to alias them to ensure that the SHAPE parser works.
- The SHAPE APPEND functions similarly to an OUTER JOIN in that 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 at the end of this article for an illustration.
- If you use an aggregate function with the SHAPE APPEND syntax, the aggregate value will occupy a field appended to the
parent resultset, which also contains the fields from the parent statement. In
contrast, the 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 COMPUTE clause when using SHAPE COMPUTE. Failure to do so results in a message that the functionality is
not supported, even though it doesn't result in a syntax error.
Examples
Simple Relation Hierarchy:
SHAPE {select * from customers}
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid)
which yields:
Customers.*
rsOrders
|
+----Orders.*
In the previous diagram, the parent recordset contains all fields
from the Customers table and a field called rsOrders. rsOrders provides a
reference to the child recordset, and contains all the fields from the Orders
table. The other examples use a similar notation.
Parameterized Hierarchy:
SHAPE {select * from customers}
APPEND ({select * from orders where customerid = ?} AS rsOrders
RELATE customerid TO PARAMETER 0)
This results in the same hierarchy as the simple relation
hierarchy.
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)
which yields:
Customers.*
rsOrders
|
+----Orders.*
rsDetails
|
+----[Order Details].*
Multiple Relation Hierarchy:
This sample illustrates a hierarchy involving 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
which yields:
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
which yields:
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
which yields:
rsOrders
|
+----cust_id
Orders.*
cust_id
Group Hierarchy with Aggregate:
NOTE: The
inner SHAPE clause in this example is identical to the statement 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
which yields:
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
which yields:
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
Note the missing
BY clause in the outer summary. This defines the Grand Total because
the parent rowset contains a single record with the grand total and a pointer
to the child recordset.
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
which yields:
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
which yields:
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.
VBA SHAPE Test Program
The following VBA program code enables you type in your own
SHAPE command and display the field hierarchy or indicate the location
of the syntax error.
WARNING: USE THE CODE PROVIDED IN THIS ARTICLE AT YOUR OWN RISK.
Microsoft provides this code "as is" without warranty of any kind, either
express or implied, including but not limited to the implied warranties of
merchantability and/or fitness for a particular purpose.
- In the ODBC Administrator of the Control Panel add a DSN
for the Microsoft Access 97 ODBC driver called OLE_DB_NWIND_JET pointing to the
Northwind (or NWIND) database.
- Create a new project. Add two text boxes (Text1, and Text2)
and a command button (Command1).
- Make both textboxes large enough to display several lines
of text and set the following properties:
Multiline: True (Visual Basic only)
Scrollbars: Vertical
Font: Courier New 10 Point
- On the Project menu, choose References and add a reference to Microsoft ActiveX Data Objects
Library.
- Add the following code:
Private Sub Command1_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
Me!Text2.Text = ""
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Provider = "MSDataShape"
cn.Open "dsn=OLE_DB_NWIND_JET"
On Error Resume Next
rs.Open Me!Text1.Text, cn, adOpenStatic, adLockReadOnly, adCmdText
If Err Then MsgBox Error
ListChapteredFields rs, 0
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Private Sub LogText(ByVal sLine As String)
If Me!Text2.Text = "" Then
Me!Text2.Text = sLine
Else
Me!Text2.Text = Me!Text2.Text & vbCrLf & sLine
End If
End Sub
Private Sub ListChapteredFields(ByVal rs As ADODB.Recordset, _
ByVal Level As Long)
Dim I As Long
For I = 0 To rs.Fields.Count - 1
LogText Space$(Level * 3) & rs(I).Name
If rs(I).Type = adChapter Then
ListChapteredFields rs(I).Value, Level + 1
End If
Next I
End Sub
- Run the project. Type the SHAPE command into Text1, click the command button, and the hierarchy
appears in Text2.
NOTE: The following text provides an example of using the Microsoft
Jet OLEDB provider with the SHAPE provider:
cn.Provider = "MSDataShape"
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0"
NOTE: If you misspell field or table names when using the Access 97
ODBC driver or JOLT providers, you will receive the following message:
Too few parameters. Expected n.
Other
providers might produce a different message.