Edit

Data Shaping Example

  • Article

The following data shaping command demonstrates how to build a hierarchical Recordset from the Customers and Orders tables in the Northwind database.

SHAPE {SELECT CustomerID, ContactName FROM Customers}   
APPEND ({SELECT OrderID, OrderDate, CustomerID FROM Orders} AS chapOrders   
RELATE customerID TO customerID)

When this command is used to open a Recordset object (as shown in Visual Basic Example of Data Shaping), it creates a chapter (chapOrders) for each record returned from the Customers table. This chapter consists of a subset of the Recordset returned from the Orders table. The chapOrders chapter contains all the requested information about the orders placed by the given customer. In this example, the chapter consists of three columns: OrderID, OrderDate, and CustomerID.

The first two entries of the resultant shaped Recordset are as follows:

CustomerID ContactName OrderID OrderDate CustomerID
ALFKI Maria Ander 10643

10692

10702

10835

10952

11011		 1997-08-25

1997-10-03

1997-10-13

1998-01-15

1998-03-16

1998-04-09		 ALFKI

ALFKI

ALFKI

ALFKI

ALFKI

ALFKI
ANATR Ana Trujillo 10308

10625

10759

10926		 1996-09-18

1997-08-08

1997-11-28

1998-03-04		 ANATR

ANATR

ANATR

ANATR

In a SHAPE command, APPEND is used to create a child Recordset related to the parent Recordset (as returned from the provider-specific command immediately after the SHAPE keyword that was discussed earlier) by the RELATE clause. The parent and child typically have at least one column in common: The value of the column in a row of the parent is the same as the value of the column in all rows of the child.

There is a second way to use SHAPE commands: namely, to generate a parent Recordset from a child Recordset. The records in the child Recordset are grouped, typically by using the BY clause, and one row is added to the parent Recordset for each resulting group in the child. If the BY clause is omitted, the child Recordset will form a single group and the parent Recordset will contain exactly one row. This is useful for computing "grand total" aggregates over the entire child Recordset.

The SHAPE command construct also enables you to programmatically create a shaped Recordset. You can then access the components of the Recordset programmatically or through an appropriate visual control. A shape command is issued like any other ADO command text. For more information, see Shape Commands in General.

Regardless of which way the parent Recordset is formed, it will contain a chapter column that is used to relate it to a child Recordset. If you want, the parent Recordset can also have columns that contain aggregates (SUM, MIN, MAX, and so on) over the child rows. Both the parent and the child Recordset can have columns that contain an expression on the row in the Recordset, as well as columns which are new and initially empty.

This section continues with the following topic.