eDocs Home > BEA AquaLogic Data Services Platform Documentation > Data Services Developer's Guide > Contents

How to Create a Logical Data Service with a Group By Clause

This topic shows how to add a group by clause to a logical data service, using the BEA extensions to XQuery.

Overview

In relational data sources, a SQL GROUP BY statement is used with aggregate functions to group retrieved data by one or more columns. If you want to retrieve a list of distinct customers and the total amount of all orders each customer has placed from a relational data source, you might use a SQL statement like this:

SELECT CUSTOMER_ID, SUM(TOTAL_ORDER_AMOUNT) FROM ORDERS
    GROUP BY CUSTOMER_ID

The output produced groups all orders by customer and then totals the order amounts for each:

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 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.

Design the Return Type Schema

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.

Return Type Schema for a Group By

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).

Create the Logical Data Service

Once you have defined the return type, create the logical data service and add the group by statement visually, using the mapping editor.

1. Create a new data space and import physical data sources (see Create Your First Data Services).
2. Create a new logical data service.
3. Click Overview, right-click the name bar, choose Associate XML Type, and select the schema file for the return type.
4. Create a primary Read function.
5. Click Query Map. Drag the primary Read function from the relevant physical data source.

At this point, do not draw additional mapping lines from the For block to the return type.

Create the Group By Node

Now create the group by node visually:

1. Right-click the element in the For block that you want to use as a grouping element, and select Create Group By.
   A Group By node is created, and mappings are automatically drawn to it. The lower section of the Group By block shows the grouping element.
    

   

2. Drag a mapping from the grouping element in the By section of the Group By node to the grouping element in the return type (here, from GroupBy CUSTOMER_ID to Return CUSTOMER_ID).
3. Drag a mapping from the appropriate element in the top section of the Group By node to the aggregate element in the return type (here, from Group By TOTAL_ORDER_AMOUNT to Return TOTAL_FOR_THIS_CUSTOMER).
    

   

Create the For Node

To map the information element, edit the XQuery code in the Source tab.

1. In the Source tab, add an XQuery for clause to the correct node in the primary Read function (here, the ORDERS node):

   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.

2. Click Query Map. Notice that a For node has been added.
    

   

Add an Aggregate Function

Last, add an aggregate function to the aggregate element in the return type (here, TOTAL_FOR_THIS_CUSTOMER).

1. In Query Map, click the aggregate element in the return type.
   Notice that it uses the fn:data function, for example:

   {fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMOUNT)}

2. Click in the expression. Make sure the Save and Cancel icons are enabled.
3. Click the Design Palette (Window > Show View > Design Palette).
4. Expand XQuery Functions, then Aggregate Functions.
5. Choose a function (here, the fn:sum function with one argument) and drag it to the expression editor. Leave the existing expression there.
6. Edit the expression to use the existing expression as an argument to the aggregate function, for example:

   {fn:sum( fn:data($CUSTOMER_ORDER_group/TOTAL_ORDER_AMOUNT) ) }

7. Click Save .

Test the Service

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.

1. Click Test.
2. At Select Operation, choose the primary Read function.
3. Click Run.

You should see data grouped by the grouping element, with a result for the aggregate element, and containing a number of information elements.

 Results of a Group By Statement

See Also

Examples

• Create Your First Data Services

Other Resources

• W3C XQuery Language Specification
• Extending XQuery for Grouping, Duplicate Elimination, and Outer Joins