Microsoft OLE DB 2.0 Programmers Reference and Data Access SDK (Microsoft Professional Editions)

A hierarchical rowset is a collection of rowsets linked together by using chapters. Hierarchies model 1:n relationships between tables, such as a master-detail relationship between Customers, Orders, and Items. A hierarchical rowset can be stored persistently in a data source object or computed by a command. This section discusses only computed hierarchies.

Chapters are identifiers of groups of details within a rowset. Chapters work as distinct collections with a beginning and an end but also share the facilities, such as accessors and notifications, of the rowset to which they belong.

The following example illustrates how a computed hierarchy is generated. The database schema contains the following three tables:

CREATE TABLE customer_table ( cust_no      INTEGER, cust_name      VARCHAR(20), city           VARCHAR(20), PRIMARY KEY (cust_no) ) CREATE TABLE order_table ( cust_no      INTEGER, ord_no         INTEGER, ord_date       DATETIME, total          DECIMAL(9,2), PRIMARY KEY (ord_no), FOREIGN KEY (cust_no) REFERENCES customer_table ) CREATE TABLE order_item_table ( ord_no       INTEGER, item_no        INTEGER, description    VARCHAR(30), quantity       SMALLINT, unit_price     DECIMAL(6,2), PRIMARY KEY (ord_no, item_no), FOREIGN KEY (ord_no) REFERENCES order_table )

The following extended SQL command generates a rowset hierarchy. The syntax of this query is used only for illustration. Providers may use other syntaxes to generate hierarchy.

SELECT (cust_no, cust_name, city, SELECT (ord_no, ord_date, total, SELECT (item_no, description, quantity, unit_price, (quantity * unit_price) AS ext_price ) FROM order_item_table WHERE order_item_table.ord_no = order_table.ord_no AS item_rows ) FROM order_table WHERE order_table.cust_no = customer_table.cust_no AS order_rows ) FROM customer_table

1998-2001 Microsoft Corporation. All rights reserved.