How To Use the ADO SHAPE Command This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
This article was previously published under Q189657 On This PageSUMMARY This article describes the ADO SHAPE command syntax for producing hierarchical recordsets, and
explains how to traverse hierarchical recordsets. VBA sample code is also
provided. 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:
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 SyntaxNOTES:
ExamplesSimple 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 ProgramThe 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.
Too few parameters. Expected n. Other
providers might produce a different message. REFERENCES ADO 2.0 Hierarchical Cursor Specification For additional information about SHAPE APPEND syntax and how to traverse hierarchical recordsets, please see the following article in the Microsoft Knowledge Base: 185425 (http://support.microsoft.com/kb/185425/EN-US/) ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java
APPLIES TO
| Article Translations
|
| United States | Change | | | All Microsoft Sites |
Help and Support
Back to the top
|
