Example 2: Aggregates

Aggregate functions produce a single value from a set of input values. An example of an aggregate function in Data View Builder is the count function, which takes a list of values and returns the number of values in the list.

The Problem

Find the number of orders placed in the Broadband database for a given customer who is also in the Wireless database.

The Solution

This query relies on a data view called "AllOrders" which retrieves customers who are in the Broadband database and also in the Wireless database. For each of these customers, the customer ID and orders are retrieved. Then, we use the Aggregate function "count" to determine how many orders are associated with a given customer. At query runtime, a customer ID is submitted as a query parameter and the result returns the number of orders associated with the given customer ID.

To create the solution, follow these steps:

• View a Demo
• Ex 2: Step 1. Locate and Configure the "AllOrders" Data View
• Ex 2: Step 2. Restart the Data View Builder and Find the New Data View
• Ex 2: Step 3. Verify the Target Schema is Saved in the Repository
• Ex 2: Step 4. Open the Data Sources and Target Schema
• Ex 2: Step 5. Map Source Nodes to Target to Project the Output
• Ex 2: Step 6. Create Two Query Parameters to be Provided at Query Runtime
• Ex 2: Step 7. Assign the Query Parameters to Source Nodes
• Ex 2: Step 8. Add the "count" Function
• Ex 2: Step 9. Verify Mappings and Conditions
• Ex 2: Step 10. View the XQuery and Run the Query to Test it
• Ex 2: Step 11. Verify the Result

View a Demo

Aggregates Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example. This demo previews the steps described in detail in the following sections. The demo assumes you already have the target schema in the server Repository and have created and configured the data view data source required for this example.

Ex 2: Step 1. Locate and Configure the "AllOrders" Data View

For this example, we will use a data view data source called AllOrders.xv. This data view is available in the Samples server repository. The path to the data_views folder in the Liquid Data server repository is:

ld_repository/data_views/

See Example directories for information on how example directory names are used.

Just in case you want to verify that you have the right data view file, the following code listing shows the XML for this data view.

Listing 9-4 XML Source for AllOrders.xv Data View File

<customers>
	{
	for $PB-BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	for $PB-WL.CUSTOMER_2 in document("PB-WL")/db/CUSTOMER
	where ($PB-WL.CUSTOMER_2/CUSTOMER_ID  eq  {--! ppright !--} $PB-BB.CUSTOMER_1/CUSTOMER_ID)
	   
	return
	<customer id={$PB-WL.CUSTOMER_2/CUSTOMER_ID}>
		<first_name>{ xf:data($PB-WL.CUSTOMER_2/FIRST_NAME) }</first_name>
		<last_name>{ xf:data($PB-WL.CUSTOMER_2/LAST_NAME) }</last_name>
		<orders>
			{
			for $PB-BB.CUSTOMER_ORDER_4 in document("PB-BB")/db/CUSTOMER_ORDER
			where ($PB-BB.CUSTOMER_1/CUSTOMER_ID  eq  {--! ppright !--} $PB-BB.CUSTOMER_ORDER_4/CUSTOMER_ID)
			return
			<order id={$PB-BB.CUSTOMER_ORDER_4/ORDER_ID} date={$PB-BB.CUSTOMER_ORDER_4/ORDER_DATE}></order>
			}
		</orders>
	</customer>
	}
</customers>

 

---

Use the WLS Administration Console to Configure Your Data View Data Source

1. Start and login to the WLS Administration Console for the Samples server you are using.

   To start the WLS Administration Console for the Liquid Data Samples server running on your local machine, type the following URL in a Web browser address field:

   http://localhost:7001/console

   Login to the console by providing the following default username and password for the Samples server.

   Table 9-1

   Field	Defaults
   Username	system
   Password	security

   User Name and Password for Samples WLS Administration Console

2. In the left pase, click the Liquid Data node.

3. In the right pane, click the Configuration tab.

4. Click the Data Sources tab.

5. Click the Data Views tab.

6. Start and login to the Administration Server. See Start the WLS Administration Console is the Getting Started guide for details.

7. In the left pane, click the Liquid Data node.

8. In the right pane, click the Configuration tab.

9. Click the Data Sources tab.

10. Click the Data Views tab.

11. Click the Configure a new Data View source description text link.

    The configuration tab for creating a new Data View Liquid Data source description is displayed.

    Figure 9-1 Configuring Liquid Data Source Description for a Data View

    

    
     

12. Fill in the fields as indicated in the following table.

    Table 9-2 Liquid Data Data View Configuration  

    Field	Description
    Name	AllOrders
    Query File	AllOrders.xv
    Schema	customerOrders.xsd

    
     

13. Click Create.

    You can click on Data Views in the breadcrumbs path at the top of the console to see the data view you added displayed in the summary table.

Ex 2: Step 2. Restart the Data View Builder and Find the New Data View

1. Restart the Data View Builder.

   If the Data View Builder was running while you configured the new data view, shut it down (menu option File—>Exit) and restart it in order to see the new data view you created show up in the Builder as a data source.

2. On the Design tab, on the Builder Toolbar, click the Sources tab, then click Data Views.

   The AllOrders.xv data view should be displayed in the list of available data views.

Ex 2: Step 3. Verify the Target Schema is Saved in the Repository

For this example, we will use a target schema called customerOrdersA.xsd. This schema is available in the Samples server repository. The path to the schemas folder in the Liquid Data server repository is:

ld_repository/schemas/

Just in case you want to verify that you have the right schema file, the following code listing shows the XML for this schema.

Listing 9-5 XML Source for customerOrdersA.xsd Target Schema File

<?xml version = "1.0" encoding = "UTF-8"?>

<xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
	<xsd:element name="customers">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="customer" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="first_name" type="xsd:string"/>
							<xsd:element name="last_name" type="xsd:string"/>
							<xsd:element name="orders" minOccurs="0" maxOccurs="unbounded">
								<xsd:complexType>
									<xsd:sequence>
										<xsd:element name="order" minOccurs="0" maxOccurs="unbounded">
											<xsd:complexType>
												<xsd:sequence>
												</xsd:sequence>
											<xsd:attribute name="id" type="xsd:string"/>
											<xsd:attribute name="date" type="xsd:string"/>
											<xsd:attribute name="amount" type="xsd:string"/>
											</xsd:complexType>
										</xsd:element>
									</xsd:sequence>
								</xsd:complexType>
							</xsd:element>
							<xsd:element name="amount" type="xsd:string"/>
						</xsd:sequence>
					<xsd:attribute name="id" type="xsd:string"/>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
</xsd:schema>

 

---

Ex 2: Step 4. Open the Data Sources and Target Schema

1. In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

2. On the Builder Design—>Sources tab, click Data Views, and double-click on AllOrders.xv to open the schema for that data source.

3. Choose File—>Set Target Schema. Use the file browser to navigate to the Repository and select CustomerOrdersA.xsd as the target schema.

   CustomerOrdersA.xsd appears as the target schema.

   This target schema is displayed as a docked schema window on the right side of the design area.

Ex 2: Step 5. Map Source Nodes to Target to Project the Output

1. Drag and drop [AllOrders]/ customers/customer/first_name from AllOrders source schema onto [CustomerOrdersA.xsd]/customers/customer/first_name in the target schema.

2. Drag and drop [AllOrders]/ customers/customer/last_name from AllOrders source schema onto [CustomerOrdersA.xsd]/customers/customer/last_name in the target schema.

Ex 2: Step 6. Create Two Query Parameters to be Provided at Query Runtime

Create two Query Parameter variables: first_name and last_name, that you can use to insert variable customer information when the query runs. Create both variables as type xs:string. Do this as follows:

1. On the Builder Toolbar, click Toolbox and then click Query Parameter.

2. From the "Type" drop-down menu, choose xs:string.

3. In Parameter Name field, enter first_name and click Add.

   The new parameter is displayed in the Query Parameters tree.

4. Repeat steps 2 and 3 to create the last_name variable.

   You should now see both parameters displayed in the Query Parameters tree.

Ex 2: Step 7. Assign the Query Parameters to Source Nodes

Assign the first_name and last_name Query Parameter variables to customer first name and last name nodes in the AllOrders data view as follows:

1. Drag and drop the first_name variable onto [allOrders]/customers/customer/first_name in the AllOrders source schema.

2. Drag and drop the last_name variable onto [allOrders]/ customers/customer/last_name in the AllOrders source schema.

Ex 2: Step 8. Add the "count" Function

Add the count function and specify the input and output as follows:

1. On the Builder Toolbar, click Toolbox and then click Functions.

2. Double-click on the count function (under Aggregate Functions)

   The count function window is displayed, showing input parameter srcval and output as some integer.

Note: Create complex or aggregate functions only on the desktop by double-clicking as described in this step. Do not attempt to drag and drop them directly into the Conditions tab.

3. Drag and drop [AllOrders]/customer/orders/order/date from the AllOrders source schema onto [count-Function]input/Parameters/srcval.

4. Drag and drop [count-Function]Output/integer to [customerOrdersA.xsd]/customers/customer/amount in the target schema.

   Note: Make sure to drag integer onto the customer amount—the last node in the fully expanded schema tree; not onto the optional orders amount?.

Ex 2: Step 9. Verify Mappings and Conditions

Your mappings should look like those shown in Figure  9-2.

Figure 9-2 Mappings for Example2: Aggregates

 

Your Conditions should like those shown in Figure  9-3.

Figure 9-3 Conditions for Example 2: Aggregates

 

Ex 2: Step 10. View the XQuery and Run the Query to Test it

1. Click on the Test tab.

   The generated XQuery for this query is shown in the following code listing.

Listing 9-6 XQuery for Example 2: Aggregates

{--	Generated by Data View Builder 1.0 	--}

<customers>
	{
	for $AllOrders.customer_1 in document("AllOrders")/customers/customer
	let $srcval_2 := 
	               for $AllOrders.order_3 in $AllOrders.customer_1/orders/order
	               where ($#first_name of type xs:string  eq  $AllOrders.customer_1/first_name)
	                 and ($#last_name of type xs:string  eq  $AllOrders.customer_1/last_name)
	               return
	               $AllOrders.order_3/@date
	let $count_4 := xf:count($srcval_2)
	where ($#first_name of type xs:string  eq  $AllOrders.customer_1/first_name)
	  and ($#last_name of type xs:string  eq  $AllOrders.customer_1/last_name)
	return
	<customer>
		<first_name>{ xf:data($AllOrders.customer_1/first_name) }</first_name>
		<last_name>{ xf:data($AllOrders.customer_1/last_name) }</last_name>
		<amount>{ $count_4 }</amount>
	</customer>
	}
</customers>

 

---

2. In the Query Parameter panel on the Test tab, set the variable values as follows:

   • last_name

   (For last_name, KAY_1 through KAY_10 are available in the data source.)

   • first_name

   (For first_name, JOHN_1 through JOHN_10 are available in the data source.)

3. Click the "Run query" button to run the query against the data sources.

Ex 2: Step 11. Verify the Result

Running this query with last_name set to "KAY_1" and first_name set to "JOHN_1" produces the following XML query result.

Listing 9-7 Result for Example 2: Aggregates

<customers>
  <customer>
    <first_name>JOHN_1</first_name>
    <last_name>KAY_1</last_name>
    <amount>2</amount>
  </customer>
</customers>

 

---