Data Services Developer's Guide : Create a Logical Data Service with a Group By Clause
This page last changed on Jan 09, 2008 by tkatz.
eDocs Home > BEA AquaLogic Data Services Platform 3.0 Documentation > Data Services Developer's Guide
|
CUSTOMER_ID | TOTAL_OF_ALL_ORDERS |
---|---|
Customer0 | 9155.10 |
Customer1 | 5336.5 |
Customer2 | 11245.05 |
Customer3 | 1419.95 |
ALDSP logical data services use XQuery 1.0 to query data. XQuery, as defined by the W3C standard, does not support group by clauses. However, ALDSP has extended XQuery to allow a group by clause in an XQuery FLWOR statement:
declare function tns:read() as element(ord1:ORDER_GROUP_BY)*{ for $CUSTOMER_ORDER in cus:CUSTOMER_ORDER() group $CUSTOMER_ORDER as $CUSTOMER_ORDER_group by $CUSTOMER_ORDER/CUSTOMER_ID as $CUSTOMER_ID_group return ...
You can add the XQuery group by statement to a logical data service visually in AquaLogic Data Services Studio. You should first make sure the service has a return type that supports the group by.
Suppose that after you retrieve all customer orders, group them by customer, and find the total amount of all orders each customer has placed, you also want a list of order IDs for each customer. You can design a logical data service to do this, doing part of the work in the mapping editor (in Studio) and part in the XQuery source.
The return type schema needs an element to group by, such as a customer ID, and an element to hold an aggregate value, such as a sum or an average. The return type can also have a complex element that contains additional elements that provide information. This example provides the list of order IDs that are totalled for each customer, as one element with multiple cardinality within a complex element.
If you want to design the schema top down using an XML editor, you can start with code like this and refactor it for your use case:
<?xml version="1.0" encoding="UTF-8"?> <xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="ld:logical/OrderGroupBy"> <xs:element name="ORDER_GROUP_BY"> <xs:complexType> <xs:sequence> <xs:element name="CUSTOMER_ID" type="xs:string"/> <xs:element name="TOTAL_FOR_THIS_CUSTOMER" type="xs:decimal"/> <xs:element name="ORDERS"> <xs:complexType> <xs:sequence> <xs:element name="ORDER_ID" type="xs:string" maxOccurs="unbounded" form="unqualified" /> </xs:sequence> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
You can also create the return type bottom up, as you design the query map (see Create Your First Data Services).
Once you have defined the return type, create the logical data service and add the group by statement visually, using the mapping editor.
At this point, do not draw additional mapping lines from the For block to the return type.
Now create the group by node visually:
To map the information element, edit the XQuery code in the Source tab.
declare function tns:read() as element(ord1:ORDER_GROUP_BY)*{ for $CUSTOMER_ORDER in cus:CUSTOMER_ORDER() group $CUSTOMER_ORDER as $CUSTOMER_ORDER_group by $CUSTOMER_ORDER/CUSTOMER_ID as $CUSTOMER_ID_group return <ord1:ORDER_GROUP_BY> <CUSTOMER_ID>{fn:data($CUSTOMER_ID_group)}</CUSTOMER_ID> <TOTAL_FOR_THIS_CUSTOMER>{fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMOUNT)}</TOTAL_FOR_THIS_CUSTOMER> <ORDERS> { for $order in $CUSTOMER_ORDER_group/ORDER_ID return <ORDER_ID>{fn:data($order)}</ORDER_ID> } </ORDERS> </ord1:ORDER_GROUP_BY> };
The for statement declares a variable (here $order) and then looks for an element ($CUSTOMER_ORDER_group/ORDER_ID) in the first group the group by statement declares (CUSTOMER_ORDER_group). The for clause then returns the value of the element using the fn:data function.
Last, add an aggregate function to the aggregate element in the return type (here, TOTAL_FOR_THIS_CUSTOMER).
{fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMOUNT)}
{fn:sum( fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMOUNT) ) }
The only way to test a logical data service with a group clause is to run the primary Read function in the Test tab. This type of data service does not have an update map, so you cannot edit data and submit it or test an Update procedure. Likewise, you cannot test a Create procedure.
You should see data grouped by the grouping element, with a result for the aggregate element, and containing a number of information elements.
Contact BEA | Feedback | Privacy | (c) 2008 BEA Systems
![]() |
Document generated by Confluence on Jan 15, 2008 11:02 |