Writing a Simple SHAPE statement

You can easily create a hierarchy of data by using a data shaping language. All you need to know is how to use the following three commands: SHAPE, APPEND, and RELATE. The basic data SHAPE syntax looks like this:

SHAPE {parent-command}

APPEND ({child-command} [[AS] table-alias]

RELATE (parent-column TO child-column)

The parent-command and child-command are often SQL SELECT statements that pull the data from the required tables. Let's look at the following example that uses the above syntax:

SHAPE {SELECT CustomerID as [Cust Id], CompanyName as [Company] FROM Customers} APPEND ({SELECT CustomerId, OrderDate, OrderId, Freight FROM Orders} AS custOrders RELATE (CustomerID TO CustomerID)

The statement above is a shaped recordset. This statement selects two fields from the Customers table and four fields from the Orders table. By using this

Part II

SHAPE statement, you can list all orders for each of the customers in the Customers table without returning any redundant information.

Notice that there are two SELECT statements in this recordset: The first SELECT statement is the parent recordset. This recordset retrieves the data from the Customers table. Notice this SELECT statement is surrounded by curly braces and preceded by the SHAPE command, which defines a recordset.

The second SELECT statement is the child recordset. It gets the data from the Orders table. Notice that this SELECT statement is also surrounded by curly braces; however, it is preceded by the APPEND clause and an opening parenthesis. The APPEND clause will add the child recordset to the parent.

^^ Note: When you append a child recordset to the parent recordset, a new field (column) is created in a parent recordset. This field is called a chapter column and has a data type called adChapter. You can use the AS clause to assign a name to the chapter column. If the appended column has no chapter-alias, a name will be generated for it automatically. In our example, the chapter column is called custOrders. Always specify an alias for your child recordset if you are planning to refer to it later in your code.

After specifying the SELECT statement for the child recordset, you must also indicate how you want the two recordsets to be linked. You do this with the RELATE clause. The column (CustomerID) from the parent recordset is related to the column (CustomerID) of the child recordset. Notice that you don't have to specify table names in the RELATE clause. Always specify the name of the parent column first.

^^ Note: The fields you use to relate parent and child recordsets must be in both recordsets. For example, you could not relate both recordsets if you did not select CustomerID from the Orders table.

Finally, remember to place a closing parenthesis at the end of the statement.

0 0

Post a comment