Shape COMPUTE Clause

Article
11/18/2022

A shape COMPUTE clause generates a parent Recordset, whose columns consist of a reference to the child Recordset; optional columns whose contents are chapter, new, or calculated columns, or the result of executing aggregate functions on the child Recordset or a previously shaped Recordset; and any columns from the child Recordset listed in the optional BY clause.

Syntax

SHAPE child-command [AS] child-alias  
   COMPUTE child-alias [[AS] name], [appended-column-list]  
   [BY grp-field-list]

Description

The parts of this clause are as follows:

child-command
Consists of one of the following:

A query command within curly braces ("{}") that returns a child Recordset object. The command is issued to the underlying data provider, and its syntax depends on the requirements of that provider. This will typically be the SQL language, although ADO does not require any particular query language.
The name of an existing shaped Recordset.
Another shape command.
The TABLE keyword, followed by the name of a table in the data provider.

child-alias
An alias used to refer to the Recordset returned by the child-command. The child-alias is required in the list of columns in the COMPUTE clause and defines the relation between the parent and child Recordset objects.

appended-column-list
A list in which each element defines a column in the generated parent. Each element contains either a chapter column, a new column, a calculated column, or a value resulting from an aggregate function on the child Recordset.

grp-field-list
A list of columns in the parent and child Recordset objects that specifies how rows should be grouped in the child.

For each column in the grp-field-list, there is a corresponding column in the child and parent Recordset objects. For each row in the parent Recordset, the grp-field-list columns have unique values, and the child Recordset referenced by the parent row consists solely of child rows whose grp-field-list columns have the same values as the parent row.

If the BY clause is included, the child Recordset's rows will be grouped based on the columns in the COMPUTE clause. The parent Recordset will contain one row for each group of rows in the child Recordset.

If the BY clause is omitted, the entire child Recordset is treated as a single group and the parent Recordset will contain exactly one row. That row will reference the entire child Recordset. Omitting the BY clause allows you to compute "grand total" aggregates over the entire child Recordset.

For example:

SHAPE {select * from Orders} AS orders             COMPUTE orders, SUM(orders.OrderAmount) as TotalSales

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

Operation

The child-command is issued to the provider, which returns a child Recordset.

The COMPUTE clause specifies the columns of the parent Recordset, which may be a reference to the child Recordset, one or more aggregates, a calculated expression, or new columns. If there is a BY clause, the columns it defines are also appended to the parent Recordset. The BY clause specifies how the rows of the child Recordset are grouped.

For example, assume you have a table, named Demographics, which consists of State, City, and Population fields. (The population figures in the table are provided solely as an example).

State	City	Population
WA	Seattle	700,000
OR	Medford	200,000
OR	Portland	400,000
CA	Los Angeles	800,000
CA	San Diego	600,000
WA	Tacoma	500,000
OR	Corvallis	300,000

Now, issue this shape command:

rst.Open  "SHAPE {select * from demographics} AS rs "  & _  
          "COMPUTE rs, SUM(rs.population) BY state", _  
           objConnection

This command opens a shaped Recordset with two levels. The parent level is a generated Recordset with an aggregate column (SUM(rs.population)), a column referencing the child Recordset (rs), and a column for grouping the child Recordset (state). The child level is the Recordset returned by the query command (select * from demographics).

The child Recordset detail rows will be grouped by state, but otherwise in no particular order. That is, the groups will not be in alphabetical or numerical order. If you want the parent Recordset to be ordered, you can use the Recordset Sort method to order the parent Recordset.

You can now navigate the opened parent Recordset and access the child detail Recordset objects. For more information, see Accessing Rows in a Hierarchical Recordset.

Resultant Parent and Child Detail Recordsets

Parent

SUM (rs.Population)	rs	State
1,300,000	Reference to child1	CA
1,200,000	Reference to child2	WA
1,100,000	Reference to child3	OR

Child1

State	City	Population
CA	Los Angeles	800,000
CA	San Diego	600,000

Child2

State	City	Population
WA	Seattle	700,000
WA	Tacoma	500,000

Child3

State	City	Population
OR	Medford	200,000
OR	Portland	400,000
OR	Corvallis	300,000