Shape Compute Clause

Shape Compute Clause

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 of parts

The parts of this clause are:

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 doesn't 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.

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 COMPUTE clause does not contain a BY clause, then there is a single parent row with an aggregate value for the entire child Recordset. If there is a BY clause, then there may be multiple parent rows, each with a reference and an aggregate value for a child Recordset.

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, then 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—Demographics—consisting of State, City, and Population fields (the population figures are solely for illustration).

State	City	Population
WA	Seattle	700,000
OR	Medford	200,000
OR	Portland	600,000
CA	Los Angeles	900,000
CA	San Diego	400,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.

You can now navigate the opened parent Recordset, and access the child detail Recordset objects. 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	900,000
CA	San Diego	400,000

Child2

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

Child3

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