Shape Append Clause

The shape command APPEND clause appends a column or columns to a Recordset. Often these columns are chapter columns, which refer to a child Recordset.

Syntax

SHAPE [parent-command [[AS] parent-alias]]

APPEND ( column-list [ [[AS] child-alias]

[RELATE parent-column TO child-column], ... ] )

[[AS] chapter-alias]

[, ... ]

Description of parts

The parentheses ("()") are a required keyword; they append a chapter column to the parent Recordset returned by the provider command.

The parts of this clause are the following:

parent-command

Zero or one of the following (you may omit the parent-command entirely):

A provider command within curly braces ("{}") that returns a 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.
Another shape command embedded in parentheses.
The TABLE keyword, followed by the name of a table.

parent-alias

An optional alias that refers to the parent Recordset.

column-list

One or more of the following:

A chapter column (indicated by parenthesis ('()'), usually surrounding a provider command within curly braces ("{}")).
The name of an existing shaped Recordset.
Another shape command embedded in parentheses.
The TABLE keyword, followed by the name of a table.
An aggregate column.
A calculated column.
A new column created with the NEW clause.

child-alias

An alias that refers to the child Recordset.

parent-column

A column in the Recordset returned by the parent-command.

child-column

A column in the Recordset returned by the child-command.

chapter-alias

An alias that refers to the chapter column appended to the parent.

...

The "parent-column TO child-column" clause is actually a list, where each relation defined is separated by a comma.

...

The clause after the APPEND keyword is actually a list, where each clause is separated by a comma, and defines another column to be appended to the parent.

Operation of Non-Parameterized Commands

The parent-command (if present) is issued and a parent Recordset is returned. Then the child-command is issued and the child Recordset is returned.

For example, the parent-command could return a Recordset of customers for a company from a Customers table, and the child-command could return a Recordset of orders for all customers from an Orders table.

SHAPE {SELECT * FROM Customers} 
   APPEND ({SELECT * FROM Orders} AS chapOrders 
   RELATE customerID TO customerID)

For non-parameterized parent-child relationships, each parent and child Recordset object must have a column in common to associate them. The columns are named in the RELATE clause, parent-column first, and then child-column. The columns may have different names in their respective Recordset objects, but must refer to the same information in order to specify a meaningful relation. For example, the Customers and Orders Recordset objects could both have a customerID field.

Data shaping appends a chapter column to the parent Recordset. The values in the chapter column are references to rows in the child Recordset, which satisfy the RELATE clause. That is, the same value is in the parent-column of a given parent row, as in the child-column of all the rows of the chapter child. When multiple TO clauses are used in the same RELATE clause, they are implicitly combined using an AND operator.

When you access the reference in the chapter column, ADO automatically retrieves the Recordset represented by the reference. Note that in a non-parameterized command, although the entire child Recordset has been retrieved, the chapter only presents a subset of rows.

If the appended column has no chapter-alias, a name will be generated for it automatically. A Field object for the column will be appended to the Recordset object's Fields collection and its data type will be adChapter.

For information about navigating a hierarchical Recordset, see Accessing Rows in a Hierarchical Recordset.

Operation of Parameterized Commands

If you are working with a large child Recordset, especially compared to the size of the parent Recordset, but only need to access a few child chapters, then you may find it more efficient to use a parameterized command.

A non-parameterized command retrieves both the entire parent and child Recordsets, appends a chapter column to the parent, and then assigns a reference to the related child chapter for each parent row.

A parameterized command retrieves the entire parent Recordset, but only retrieves the chapter Recordset when the chapter column is accessed. This difference in retrieval strategy can yield significant performance benefits.

For example, you can specify the following:

SHAPE {SELECT * FROM customer} 
   APPEND ({SELECT * FROM orders WHERE cust_id = ?} 
   RELATE cust_id TO PARAMETER 0)

The parent and child tables have a column name in common, cust_id. The child-command has a "?" placeholder, to which the RELATE clause refers (that is, "...PARAMETER 0").

Note The PARAMETER clause pertains solely to the shape command syntax. It is not associated with either the ADO Parameter object or Parameters collection.

When the parameterized shape command is executed, the following happens:

The parent-command is executed and returns a parent Recordset from the Customers table.
A chapter column is appended to the parent Recordset.
When the chapter column of a parent row is accessed, the value of the customer.cust_id column replaces the placeholder for orders.cust_id, and the child-command is executed.
All the rows from the Orders table where the value of the orders.cust_id column matches the value of the customer.cust_id column, are retrieved. By default the child Recordsets will be cached on the client until all references to the parent Recordset are released. To change this behavior, set the Recordset dynamic property Cache Child Rows to False.
A reference to the retrieved child rows (that is, the chapter of the child Recordset) is placed in the chapter column of the current row of the parent Recordset.
Steps 3-5 are repeated when the chapter column of another row is accessed.

The Cache Child Rows dynamic property is set to True by default. The caching behavior varies depending upon the parameter values of the query. In a query with a single parameter, the child recordset for a given parameter value will be cached between requests for a child with that value. The following code demonstrates this:

...
SCmd = "SHAPE {select * from customer} " & _
         "APPEND({select * from orders where cust_id = ?} " & _
         "RELATE cust_id TO PARAMETER 0) AS chpCustOrder"
Rst1.Open sCmd, Cnn1
Set RstChild = Rst1("chpCustOrder").Value
Rst1.MoveNext      ' Next cust_id passed to Param 0, & new rs fetched 
                   ' into RstChild.
Rst1.MovePrev      ' RstChild now holds cached rs, saving round trip.
...

In a query with two or more parameters, a cached child is used only if all the parameter values match the cached values.

Hybrid Commands

Hybrid commands are partially parameterized commands. For example:

SHAPE {select * from plants} 
   APPEND( {select * from customers where country = ?} 
           RELATE PlantCountry TO PARAMETER 0, 
             PlantRegion TO CustomerRegion )

The caching behavior for a hybrid command is the same as that of regular parameterized command.

Intervening Shape COMPUTE Clauses

It is valid to embed the parameterized command of a parameterized shape command in an arbitrarily nested number of shape COMPUTE commands, as in the following example.

SHAPE {select au_lname, state from authors} APPEND 
   ((SHAPE 
      (SHAPE 
         {select * from authors where state = ?} rs 
      COMPUTE rs, ANY(rs.state) state, ANY(rs.au_lname) au_lname 
      BY au_id) rs2 
   COMPUTE rs2, ANY(rs2.state) BY au_lname) 
RELATE state TO PARAMETER 0)