Examples SECTION 12 Databases
198
12-10 Examples
12-10-1 Simple Relation Hierarchy example:
SHAPE {select * from customers}
APPEND ({select * from orders} AS rsOrders
RELATE customerid TO customerid)
The parent recordset contains all fields from the Customers table and a field
called rsOrders. rsOrders provides a reference to the child recordset, and
contains all the fields from the Orders table. The other examples use a similar
notation.
12-10-2 Compound Relation Hierarchy example:
This sample illustrates a three-level hierarchy of customers, orders, and order
details:
SHAPE {SELECT * from customers}
APPEND ((SHAPE {select * from orders}
APPEND ({select * from [order details]} AS
rsDetails
RELATE orderid TO orderid)) AS rsOrders
RELATE customerid TO customerid)
In addition to the Simple Relation Hierarchy example, the Order ID is now
used to create a recordset containing the order details for the shown order.
12-10-3 Hierarchy with Aggregate example:
SHAPE (select * from orders}
APPEND ({select od.orderid, od.UnitPrice * od.quantity
as ExtendedPrice
from [order details] As od}
RELATE orderid TO orderid) As rsDetails,
SUM(ExtendedPrice) AS OrderTotal
This example creates a Recordset of all the orders and their details, and adds
a field called ExtendedPrice to store the total order value for each record, and
sums all the ExtendedPrice values which is stored in Order Total.
12-10-4 Group Hierarchy example:
SHAPE {select customers.customerid AS cust_id,
orders.*
from customers inner join orders on
customers.customerid =
orders.customerid} AS rsOrders
COMPUTE rsOrders BY cust_id
12-10-5 Group Hierarchy with Aggregate example:
SHAPE
SHAPE {select customers.*, orders.orderid,
orders.orderdate fromc ustomers inner join orders
on customers.customerid = orders.customerid}
APPEND ({select od.orderid,od.unitprice *
od.quantity as ExtendedPrice from order details]as
od} AS rsDetails
RELATE orderid TO orderid),
SUM(rsDetails.ExtendedPrice) AS OrderTotal) AS
rsOrders
To Next Page
Loading...
