Skip navigation.
Downloads | Product Documentation | Support

Liquid Data by Example

# Query Cookbook

This section provides examples of BEA Liquid Data for WebLogic queries using some of the advanced features and tools offered in the Data View Builder. This book assumes that you are familiar with the Data View Builder user interface and that you have an understanding of the basic concepts and tasks using the Data View Builder. For details on using the Data View Builder, see Building Queries and Data Views.

The following use cases and examples are provided here to give you a jump-start for constructing real-world queries to solve common problems. Each use case includes a viewlet demo of building the solution using Data View Builder. Watching a viewlet takes 3 to 5 minutes.

- Example 1: Simple Joins (View a Demo)
- Example 3: Aggregates (View a Demo)
- Example 4: Date and Time Duration (View a Demo)
- Example 5: Union (View a Demo)
- Example 6: Minus (View a Demo)
- Example 7: Complex Parameter Type (CPT) (View a Demo)

For an example of using a stored procedure in a query, see Example: Defining and Using a Customer Orders Stored Procedure.

Each use case has an example with a description of the problem and the steps to solve the problem. The examples use two databases:

- The BroadBand database (`PB-BB`) contains "BroadBand" subscribers and service orders
- The Wireless database (`PB-WL`) contains "Wireless" subscribers.

In cases where the target schemas do not already exist in the Liquid Data Samples Server repository, they are provided in this documentation along with the examples. You can cut-and-paste the schema content into a`.xsd` file to construct your own target schemas. (You can also copy from the PDF version of this document which may give you a copy that formats better your text editor.)

Note: To find out what data are contained in any data source, create a new "test" project, open the source schema you are interested in, and map key source nodes to any appropriate target schema. (For example, map customer first and last names and customer ID from source to target schemas.) Then click on Test tab and choose Query—>Run Query. The result will return all customers in the data source queried.

As you work through the examples, remember to save any projects that you want to keep before creating new ones.

# Example 1: Simple Joins

A join merges data from two data sources based on a certain relation.

## The Problem

For each Wireless Customer ID, determine whether the customer has any BroadBand orders. Assume that the Customer ID matches across databases.

## The Solution

First, you want to find matching BroadBand customers (who are also included in the Wireless database), then return BroadBand Order IDs for the matching customers. Because Customer IDs in the Wireless database align with those in BroadBand, we can find matching BroadBand customers with a simple join of Wireless Customer IDs with the Customer IDs in the BroadBand order information.

To create the solution, follow these steps:

- View a Demo
- Ex 1: Step 1. Verify the Target Schema is Saved in Repository
- Ex 1: Step 2. Open Source and Target Schemas
- Ex 1: Step 3. Map Nodes from Source to Target Schema to Project the Output
- Ex 1: Step 4. Create a Query Parameter for a Customer ID to be Provided at Query Runtime
- Ex 1: Step 5. Assign the Query Parameter to a Source Node
- Ex 1: Step 6. Join the Wireless and BroadBand Customer IDs
- Ex 1: Step 7. View the XQuery and Run the Query to Test it
- Ex. 1: Step 8. Verify the Result

### View a Demo

Simple Joins 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 Liquid Data Samples Server repository.

### Ex 1: Step 1. Verify the Target Schema is Saved in Repository

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

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/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 2-1 XML Source for customerOrders.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 ref = "customer" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "customer">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref = "first_name"/>
>     				<xsd:element ref = "last_name"/>
>     				<xsd:element ref = "orders" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     			<xsd:attribute name = "id" use = "optional" type = "xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "first_name" type = "xsd:string"/>
>     	<xsd:element name = "last_name" type = "xsd:string"/>
>     	<xsd:element name = "orders">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref = "order" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "order">
>     		<xsd:complexType>
>     			<xsd:attribute name = "id" use = "optional" type = "xsd:string"/>
>     			<xsd:attribute name = "date" use = "optional" type = "xsd:string"/>
>     			<xsd:attribute name = "amount" use = "optional" type = "xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     </xsd:schema>

### Ex 1: Step 2. Open Source and Target Schemas

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 Relational Databases and open two data sources:

- Double-click on the `PB-WL` (Wireless) relational database to open the schema for this data source.
- Double-click on the `PB-BB` (BroadBand) relational database to open the schema for this data source.

3. Choose the menu option File—>Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the `customerOrders.xsd` schema. Choose `customerOrders.xsd` and click Open.

`customerOrders.xsd` appears as the target schema.

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

4. Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

### Ex 1: Step 3. Map Nodes from Source to Target Schema to Project the Output

1. Drag and drop `[PB-WL]db/CUSTOMER/CUSTOMER_ID` from source schema onto the target schema `[customerOrders.xsd]/customers/customer/id`
2. Drag and drop `[PB-WL]db/CUSTOMER/FIRST_NAME` from source schema onto the target schema `[customerOrders.xsd]/customers/customer/first_name`
3. Drag and drop `[PB-WL]db/CUSTOMER/LAST_NAME` from source schema onto the target schema `[customerOrders.xsd]/customers/customer/last_name`
4. Drag and drop `[PB-BB]db/CUSTOMER_ORDER/ORDER_DATE` onto the target schema `[customerOrders.xsd]customers/customer/orders/order/order_date`
5. Drag and drop `[PB-BB]db/CUSTOMER_ORDER/ORDER_ID` onto the target schema `[customerOrders.xsd]customers/customer/orders/order/id`

### Ex 1: Step 4. Create a Query Parameter for a Customer ID to be Provided at Query Runtime

Create a Query Parameter wireless_id variable for a Wireless Customer ID that you will supply at query execution time:

1. On the Builder Design, click Toolbox and then click Query Parameter.
2. From the Type drop-down menu, choose `xs:string`.
3. In Parameter Name field, enter `wireless_id` and click Add.

The new parameter is displayed in the Query Parameters tree.

### Ex 1: Step 5. Assign the Query Parameter to a Source Node

Drag and drop the wireless_id query parameter to [PB-WL]db/CUSTOMER/CUSTOMER_ID.

### Ex 1: Step 6. Join the Wireless and BroadBand Customer IDs

Drag and drop `[PB-WL]db/CUSTOMER/CUSTOMER_ID` to `[PB-BB]db/CUSTOMER_ORDER/CUSTOMER_ID`

### Ex 1: Step 7. 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 2-2 XQuery for Example 1: Simple Joins

>     <customers>
>     	{
>     	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	where ($#wireless_id of type xs:string  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
>     	return
>     	<customer id={$PB_WL.CUSTOMER_1/CUSTOMER_ID}>
>     		<first_name>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</first_name>
>     		<last_name>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</last_name>
>     		<orders>
>     			{
>     			for $PB_BB.CUSTOMER_ORDER_2 in document("PB-BB")/db/CUSTOMER_ORDER
>     			where ($PB_WL.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_2/CUSTOMER_ID)
>     			return
>     			<order id={$PB_BB.CUSTOMER_ORDER_2/ORDER_ID} date={cast as xs:string($PB_BB.CUSTOMER_ORDER_2/ORDER_DATE)}></order>
>     			}
>     		</orders>
>     	</customer>
>     	}
>     </customers>

1. Set the variable value to submit to the query when the query runs. To do this, you need to enter a value in the Query Parameter panel. Click into the cell under Value and enter CUSTOMER_3.

(Customer IDs CUSTOMER_1 through CUSTOMER_10 are available in the data source to try.)

2. Click the Run query button to run the query against the data sources.

### Ex. 1: Step 8. Verify the Result

Running this query with the `wireless_id` parameter set to CUSTOMER_3 produces the following XML query result.

Listing 2-3 Result for Example 1: Simple Joins

>     <customers>
>       <customer id="CUSTOMER_3">
>         <first_name>JOHN_3</first_name>
>         <last_name>KAY_3</last_name>
>         <orders>
>           <order date="2002-03-06-08:00" id="ORDER_ID_3_0"/>
>           <order date="2002-03-06-08:00" id="ORDER_ID_3_1"/>
>           <order date="2002-03-06-08:00" id="ORDER_ID_3_2"/>
>           <order date="2002-03-06-08:00" id="ORDER_ID_3_3"/>
>         </orders>
>       </customer>
>     </customers>

## Example 2: Retrieving Information

## The Problem

Find Customer IDs for customers who have both Wireless and BroadBand accounts and include in the generated data the state each customer resides in.

## The Solution

This example shows how to do the following:

- Project output
- Specify the order of the result

### Open Data Sources and Add a Target Schema

1. Choose File —> New Project to clear your desktop and reset all default values.
2. On the Builder Toolbar —> Sources tab, click Relational Databases and open two data sources:

- Double-click on the `PB-WL` relational database to open the schema for this data source.
- Double-click on the `PB-BB` relational database to open the schema for this data source.

3. Choose File —> Set Target Schema. Use the file browser to navigate to the Liquid Data Samples Server repository and select `amtByState.xsd` as the target schema.

Note: If `amtByState.xsd` is not already available from the repository, you can create and save it yourself. For a copy of the schema file and instructions on how to save it to the Liquid Data Samples Server repository, see Target Schemas in Building Queries and Data Views. The schema itself is shown in Listing 5-1 of the same book.

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

### Map Elements from Source to Target Schema to Project Output

To project Customer first and last names and state to Target, do the following:

1. Drag and drop Wireless `[PB-WL]/FIRST_NAME` (under `CUSTOMER*`) onto `FIRST_NAME` in the Target schema.
2. Drag and drop Wireless `[PB-WL]/LAST_NAME` (under `CUSTOMER*`) onto `LAST_NAME` in the Target schema.
3. Drag and drop Wireless `[PB-WL]/STATE` (under `CUSTOMER*`) onto `STATE` (under `CUSTOMER*`) in the Target schema.

### Join Two Sources

To create a join between Wireless [`PB-WL`] and BroadBand [`PB-BB`] on customer IDs, do the following:

- Drag and drop BroadBand `[PB-BB]/CUSTOMER_ID` (under CUSTOMER*) onto the associated Wireless `[PB-WL]/CUSTOMER_ID` element.

The following shows the mappings in the Data View Builder.

Figure 2-1 Query to Identify Customers by Customer ID and Sort by State

### Specify the Order of the Result Using the Sort By Features

To order the output alphabetically by State do the following:

1. Click the Sort By tab.

This tab shows repeatable elements in the target schema with subordinate fields that you can select for ordering.

2. From the drop-down menu choose CUSTOMER, and then click into the Direction cell next to STATE and set STATE to Ascending.

This will cause the query to display the results in ascending order by state.

### View and Run the Query

Now that you have built the query, you can switch to the Test tab to view the generated XQuery and run the query to see the kind of result it returns.

1. Click on the Test tab.

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

Listing 2-4 XQuery for Example: Query Customers by ID and Sort by State

>     <customers>
>     	{
>     	for $PB_WL.CUSTOMER_1 in document("PB-WL")/db/CUSTOMER
>     	where xf:not(xf:empty(
>     		for $PB_BB.CUSTOMER_2 in document("PB-BB")/db/CUSTOMER
>     		where ($PB_BB.CUSTOMER_2/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_1/CUSTOMER_ID)
>     		return
>     		xf:true()))
>     	return
>     	<CUSTOMER>
>     		<FIRST_NAME>{ xf:data($PB_WL.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
>     		<LAST_NAME>{ xf:data($PB_WL.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
>     		<STATE>{ xf:data($PB_WL.CUSTOMER_1/STATE) }</STATE>
>     	</CUSTOMER>
>     	sortby(STATE ascending)
>     	}
>     </customers>

1. Click the Run query button to run the query against the data sources.

Querying these data sources as described in this example produces the XML query result shown in the following code listing.

Figure 2-2 XML Result for Example: Query Customers by ID and Sort by State

>     <customers>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_3</FIRST_NAME>
>         <LAST_NAME>KAY_3</LAST_NAME>
>         <STATE>AZ</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_8</FIRST_NAME>
>         <LAST_NAME>KAY_8</LAST_NAME>
>         <STATE>AZ</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_10</FIRST_NAME>
>         <LAST_NAME>KAY_10</LAST_NAME>
>         <STATE>CA</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_5</FIRST_NAME>
>         <LAST_NAME>KAY_5</LAST_NAME>
>         <STATE>CA</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_4</FIRST_NAME>
>         <LAST_NAME>KAY_4</LAST_NAME>
>         <STATE>NV</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_9</FIRST_NAME>
>         <LAST_NAME>KAY_9</LAST_NAME>
>         <STATE>NV</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_1</FIRST_NAME>
>         <LAST_NAME>KAY_1</LAST_NAME>
>         <STATE>TX</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_6</FIRST_NAME>
>         <LAST_NAME>KAY_6</LAST_NAME>
>         <STATE>TX</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_2</FIRST_NAME>
>         <LAST_NAME>KAY_2</LAST_NAME>
>         <STATE>WA</STATE>
>       </CUSTOMER>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN_7</FIRST_NAME>
>         <LAST_NAME>KAY_7</LAST_NAME>
>         <STATE>WA</STATE>
>       </CUSTOMER>
>     </customers>

# Example 3: 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 3: Step 1. Configure the "AllOrders" Stored Query as a Data View
- Ex 3: Step 2. Restart the Data View Builder and Find the New Data View
- Ex 3: Step 3. Verify that the Target Schema is Saved in the Repository
- Ex 3: Step 4. Open the Data Sources and Target Schema
- Ex 3: Step 5. Map Source Nodes to Target to Project the Output
- Ex 3: Step 6. Create Two Query Parameters to be Provided at Query Runtime
- Ex 3: Step 7. Assign the Query Parameters to Source Nodes
- Ex 3: Step 8. Add the Count XQuery Function
- Ex 3: Step 9. Verify Mappings and Conditions
- Ex 3: Step 10. View the XQuery and Test by Running the Query
- Ex 3: 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 Liquid Data Samples Server repository and have created and configured the data view data source required for this example.

### Ex 3: Step 1. Configure the "AllOrders" Stored Query as a Data View

For this example, we will use a data view data source called `AllOrders.xv`. However, before this data view can be used, it must first be created.

Figure 2-3 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 $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 $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 browser address field:

>     http://localhost:7001/console

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

Table 2-4

Field | Defaults
--- | ---
Username | `system`
Password | `security`

User Name and Password for Samples WLS Administration Console

2. In the left pane, click the Liquid Data node.
3. In the right pane click the Stored Queries tab.
4. Locate `AllOrders` among the queries (it will be near the bottom of the listing).
5. Click the Configure link.
6. For a schema enter `customerOrders.xsd` or browse to the file. (You do not need to enter a namespace URI or schema root element name for this example.)
7. Click Create.

Figure 2-5 Configuring Liquid Data Source Description for a Data View

8. Find all orders in the list of stored queries (now it should be near the top of the listing).
9. Click the Create Data View link.

The Create a Data View dialog box is displayed.

10. Provide a name such as `AllOrders` for your new data view, then click Create.

Available data views will display. `AllOrders` should appear in the list.

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

1. Reconnect to the Data View Builder by selecting File—>Connect.
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 3: Step 3. Verify that 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 Liquid Data Samples Server repository. The path to the schemas folder is:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/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 2-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 3: 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 Liquid Data Samples Server repository. Select `CustomerOrdersA.xsd` as the target schema. This target schema is displayed as a docked schema window on the right side of the design area.

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

1. Drag and drop `[AllOrders]/ customers/customer/first_name` from the 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 3: 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 3: 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 3: Step 8. Add the Count XQuery Function

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

1. On the Builder Toolbar, click Toolbox and then click XQuery 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 result.

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.

1. Drag and drop `[AllOrders]/customer/orders/order/date` from the AllOrders source schema onto `[count-Function]input/Parameters/srcval`.
2. Drag and drop `[count-Function]Output/result` to `[customerOrdersA.xsd]/customers/customer/amount` in the target schema.

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

### Ex 3: Step 9. Verify Mappings and Conditions

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

Figure 2-6 Mappings for Example2: Aggregates

Your Conditions should like those shown in Figure 2-7.

Figure 2-7 Conditions for Example 2: Aggregates

### Ex 3: Step 10. View the XQuery and Test by Running the Query

1. Click on the Test tab.

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

Listing 2-6 XQuery for Example 2: Aggregates

>     namespace view = "urn:views"
>     <customers>
>     	{
>     	for $view:AllOrders.customer_2 in view:AllOrders()/customers/customer
>     	let $srcval_3 :=
>     	               for $view:AllOrders.orders_5 in $view:AllOrders.customer_2/orders
>     	               for $view:AllOrders.order_6 in $view:AllOrders.orders_5/order
>     	               return
>     	               xf:data($view:AllOrders.order_6/@date)
>     	where ($#last_name of type xs:string  eq  $view:AllOrders.customer_2/last_name)
>     	  and ($#first_name of type xs:string  eq  $view:AllOrders.customer_2/first_name)
>     	return
>     	<customer>
>     		<first_name>{ xf:data($view:AllOrders.customer_2/first_name) }</first_name>
>     		<last_name>{ xf:data($view:AllOrders.customer_2/last_name) }</last_name>
>     		<amount>{ cast as xs:string(xf:count($srcval_3)) }</amount>
>     	</customer>
>     	}
>     </customers>

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

2. Click the Run query button to run the query against the data sources.

### Ex 3: 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 2-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>

# Example 4: Date and Time Duration

Data View Builder supports a set of functions that operate on date and time. (For more information on date and time functions see Date and Time Functions in the XQuery Reference Guide.)

## The Problem

Determine if a BroadBand customer has any open orders in the BroadBand database before a specified date.

## The Solution

For each BroadBand order that matches the given Customer ID, you need to set these conditions:

- The order status is "OPEN"
- The ship date for a given customer_id is earlier than or equal to the date (date1) provided. (customer_id and date1 are a variables that you define as query parameters to be submitted at query runtime).

To create the solution, follow these steps:

- View a Demo
- Ex 4: Step 1. Verify the Target Schema is Saved in Repository
- Ex 4: Step 2. Open Source and Target Schemas
- Ex 4: Step 3. Map Source to Target Nodes to Project the Output
- Ex 4: Step 4. Create Joins
- Ex 4: Step 5. Create Two Query Parameters for Customer ID and Date to be Provided at Query Runtime
- Ex 4: Step 6. Set a Condition Using the Customer ID
- Ex 4: Step 7. Set a Condition to Determine if Order Ship Date is Earlier or Equal to a Date Submitted at Query Runtime
- Ex 4: Step 8. Set a Condition to Include Only "Open" Orders in the Result
- Ex 4: Step 9. View the XQuery and Run the Query to Test it
- Ex 4: Step 9. Verify the Result

### View a Demo

Date and Time Duration 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 Liquid Data Samples Server repository.

### Ex 4: Step 1. Verify the Target Schema is Saved in Repository

For this example, we will use a target schema called `customerLineItems.xsd`. This schema is available in the Liquid Data Samples Server repository.

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/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 2-8 XML Source for customerLineItems.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 ref = "customer" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "customer">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref = "first_name"/>
>     				<xsd:element ref = "last_name"/>
>     				<xsd:element ref = "orders" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "first_name" type = "xsd:string"/>
>     	<xsd:element name = "last_name" type = "xsd:string"/>
>     	<xsd:element name = "orders">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref = "order" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "order">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element ref = "line_item" minOccurs = "0" maxOccurs = "unbounded"/>
>     			</xsd:sequence>
>     			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
>     			<xsd:attribute name = "date" use = "required" type = "xsd:string"/>
>     			<xsd:attribute name = "amount" use = "required" type = "xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:element name = "line_item">
>     		<xsd:complexType>
>     			<xsd:attribute name = "id" use = "required" type = "xsd:string"/>
>     			<xsd:attribute name = "product" use = "required" type = "xsd:string"/>
>     			<xsd:attribute name = "status" use = "required" type = "xsd:string"/>
>     			<xsd:attribute name = "expected_ship_date" use = "required" type = "xsd:string"/>
>     		</xsd:complexType>
>     	</xsd:element>
>     </xsd:schema>

### Ex 4: Step 2. Open Source and Target Schemas

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 Relational Databases and open one data source:

- Double-click on the `PB-BB` (BroadBand) relational database to open the schema for this data source.

3. Choose the menu option File—>Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the `customerLineItems.xsd` schema. Choose `customerLineItems.xsd` and click Open.

`customerLineItems.xsd` appears as the target schema.

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

4. Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

### Ex 4: Step 3. Map Source to Target Nodes to Project the Output

Project the output values as follows.

1. Drag and drop `[PB-BB]/db/CUSTOMER/FIRST_NAME` from the source schema onto `[customerLineItems.xsd]/customers/customer/first_name` in the target schema.
2. Drag and drop `[PB-BB]/db/CUSTOMER/LAST_NAME` from the source schema onto `[customerLineItems.xsd]/customers/customer/last_name` in the target schema.
3. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/LINE_ID` from the source schema onto `[customerLineItems.xsd]/customers/customer/orders/order/line_item/id` in the target schema (`id` is an attribute of line_item).
4. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/PRODUCT_NAME` from the source schema onto `[customerLineItems.xsd]/customers/customer/orders/order/line_item/product` in the target schema (`product` is an attribute of line_item).
5. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/STATUS` from the source schema `[customerLineItems.xsd]/customers/customer/orders/order/line_item/status` in the target schema (`status` is an attribute of line_item).
6. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_
   SHIP_DATE` from the source schema `[customerLineItems.xsd]/customers/customer/orders/order/line_item/expected_ship_date` in the target schema (`expected_ship_date` is an attribute of line_item).

At this point, the following mappings should be displayed on the Mappings tab. (Getting the mappings in the same order as shown is not as important as verifying that the relationships between source and target nodes are the same. The @ symbols indicate attributes.)

Source | Target
--- | ---
`[PB-BB]/db/CUSTOMER/FIRST_NAME` | `[customerLineItems.xsd]/customers/customer/ first_name`
`[PB-BB]/db/CUSTOMER/LAST_NAME` | `[customerLineItems.xsd]/customers/customer/ last_name`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/LINE_ID` | `[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@id`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/PRODUCT_NAME` | `[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@product`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/STATUS` | `[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@status`
`[PB-BB]/db/CUSTOMER/CUSTOMER_ ORDER_LINE_ITEM/EXPECTED_SHIP_ DATE` | `[customerLineItems.xsd]/customers/customer/ orders/order/line_item/@expected_ship_date`

### Ex 4: Step 4. Create Joins

Join customer with corresponding line-item data. This requires two joins, one to find the customer's Order IDs, and another that uses the Order IDs and finds the corresponding line-item information:

1. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto `[PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID`
2. Drag and drop `[PB-BB]/db/CUSTOMER_ORDER/ORDER_ID` onto `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID`

### Ex 4: Step 5. Create Two Query Parameters for Customer ID and Date to be Provided at Query Runtime

Create two Query Parameter variables: customer_id and date1, that you can use to insert as variable values 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 `customer_id` and click Add.

The new parameter is displayed in the Query Parameters tree.

4. Repeat steps 2 and 3 to create the `date1` variable.

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

### Ex 4: Step 6. Set a Condition Using the Customer ID

1. On the Builder Toolbar, click Toolbox and then click XQuery Functions.
2. Drag and drop the equals [`eq`] function (under Comparison operators) onto the next empty row in the Conditions tab.

The Functions Editor appears, displaying placeholder variables for you to fill in.

3. On the Builder Toolbar, click on Query Parameter, then drag customer_id onto anyValue1 onto the left side of the equation.
4. Drag `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto the right side of the equation.

The function should look like this:

>     (customer_id eq [PB-BB]/db/CUSTOMER/CUSTOMER_ID)

5. Close the Functions Editor.

### Ex 4: Step 7. Set a Condition to Determine if Order Ship Date is Earlier or Equal to a Date Submitted at Query Runtime

1. Click on XQuery Functions, and drag and drop the Operator function [`le`] (less than or equal) onto the next empty row on the Conditions tab.

The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

2. Drag and drop
   `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE` onto anyValue1 on the left side of the equation.
3. Click on XQuery Functions, and drag and drop the `date-from-string-with-format` function (under Data and Time functions) onto anyValue2 on the right side of the equation.

At this point, the expression in the Functions Editor should appear as:

>     ([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE le  xfext:date-from-string-with-format(pattern,srcval))

1. Click Constants, enter the following in the String field:

>     yyyy-MM-dd

Now drag it (via the Constant icon next to the field) onto pattern (first placeholder parameter to the date function).

2. Click on Query Parameter, and drag and drop date1 from the Query Parameters panel onto srcval (the second placeholder parameter to the date function).

The completed expression should look like this:

>     ([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",date1))

1. Close the Functions Editor.

The condition you created is displayed on the Conditions tab in the Source column.

### Ex 4: Step 8. Set a Condition to Include Only "Open" Orders in the Result

Set the second condition to an Open Order status.

1. Click on XQuery Functions, and drag and drop the Comparison operator [`eq`] (equal) function onto the Conditions tab.

The Functions Editor pops up and displays a statement with placeholder variables for you to fill in.

2. For the left parameter (anyValue1), drag and drop `[PB-BB]/db/customer_order_line_item/status` on to anyValue1.
3. For the right parameter (anyValue2), create a constant String with a value of OPEN, and drop it (via the Constant icon next to the field) onto anyValue2.

The completed expression should look like this:

>     ([PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS eq "OPEN")

Close the Functions Editor.

### Ex 4: Step 9. 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 2-9 XQuery for Example 3: Date and Time Duration

>     <customers>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	where ($#customer_id of type xs:string  eq  $PB_BB.CUSTOMER_1/CUSTOMER_ID)
>     	return
>     	<customer>
>     		<first_name>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</first_name>
>     		<last_name>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</last_name>
>     		<orders>
>     			<order>
>     				{
>     				for $PB_BB.CUSTOMER_ORDER_LINE_ITEM_4 in document("PB-BB")/db/CUSTOMER_ORDER_LINE_ITEM
>     				where xf:not(xf:empty(
>     					for $PB_BB.CUSTOMER_ORDER_5 in document("PB-BB")/db/CUSTOMER_ORDER
>     					where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_5/CUSTOMER_ID)
>     					  and ($PB_BB.CUSTOMER_ORDER_5/ORDER_ID  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/ORDER_ID)
>     					return
>     					xf:true()))
>     				  and ($PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/STATUS  eq  "OPEN")
>     				  and ($PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/EXPECTED_SHIP_DATE  le  xfext:date-from-string-with-format("yyyy-MM-dd",$#date1 of type xs:string))
>     				return
>     				<line_item id={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/LINE_ID} product={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/PRODUCT_NAME} status={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/STATUS} expected_ship_date={$PB_BB.CUSTOMER_ORDER_LINE_ITEM_4/EXPECTED_SHIP_DATE} />
>     				}
>     			</order>
>     		</orders>
>     	</customer>
>     	}
>     </customers>

1. In the Query Parameter panel on the Test tab, set the variable values for customer_id and date1 to submit to the query when the query runs.

For example:

- customer_id: CUSTOMER_1 (CUSTOMER_1 through CUSTOMER_10 are available in the data source.)
- date1: 2002-08-01 (You can enter any date in the form yyyy-MM-dd.)

2. Click the Run query button to run the query against the data sources.

### Ex 4: Step 9. Verify the Result

Running this query with `customer_id` set to CUSTOMER_1 and `date1` set to 2002-08-01 produces the following XML query result.

Listing 2-10 Result for Example 3: Date and Time Duration

>     <customers>
>       <customer>
>         <first_name>JOHN_B_1</first_name>
>         <last_name>KAY_1</last_name>
>         <orders>
>           <order>
>             <line_item id="LINE_ID_1" product="RBBC01" status="OPEN" expected_ship_date="2002-03-06"/>
>             <line_item id="LINE_ID_3" product="BN16" status="OPEN" expected_ship_date="2002-03-06"/>
>             <line_item id="LINE_ID_5" product="CS100" status="OPEN" expected_ship_date="2002-03-06"/>
>             <line_item id="LINE_ID_1" product="RBBC01" status="OPEN" expected_ship_date="2002-03-06"/>
>             <line_item id="LINE_ID_3" product="BN16" status="OPEN" expected_ship_date="2002-03-06"/>
>             <line_item id="LINE_ID_5" product="CS100" status="OPEN" expected_ship_date="2002-03-06"/>
>           </order>
>         </orders>
>       </customer>
>     </customers>

# Example 5: Union

A union query is equivalent to concatenating two or more subordinate queries, and pooling the query results into the same output. There are two important rules for a union query.

- Each subordinate query produces a result directed at a repeatable target schema node that is not shared (parent or child) with any other subordinate query target.
- You cannot specify any conditions across these subordinate queries.

## The Problem

For any BroadBand Customer ID, list any BroadBand and Wireless orders. Assume the Customer IDs match across databases.

## The Solution

This query requests a union of BroadBand orders and Wireless orders. Remember that a union retrieves data from multiple sources, such as the BroadBand and Wireless databases, but there are no conditions for the query. If you specify any condition, such as matching order dates, then you are creating a join query. In this example, you need a target schema that contains a repeatable list of Customer IDs, and within that list, a repeatable list of orders. Then you will clone the orders element, using one element for BroadBand orders and the other element for Wireless orders.

To create the solution, follow these steps:

- View a Demo
- Ex 5: Step 1. Verify the Target Schema is Saved in Repository
- Ex 5: Step 2. Open Source and Target Schemas
- Ex 5: Step 3. Clone the Orders Element of the Target Schema
- Ex 5: Step 4. Create a Query Parameter for a Customer ID
- Ex 5: Step 5. Assign a Query Parameters
- Ex 5: Step 6. Define Source Relationships
- Ex 5: Step 7. Project the Output to the Target Schema
- Ex 5: Step 8. View, then Run the Query
- Ex 5: Step 9. Verify the Result

### View a Demo

Union 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 Liquid Data Samples Server repository.

### Ex 5: Step 1. Verify the Target Schema is Saved in Repository

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

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/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 2-11 XML Source for unionOrders.xsd Target Schema File

>     <?xml version = "1.0" encoding = "UTF-8"?>
>     <!--Generated by Data View Builder 1.1. Conforms to w3c http://www.w3.org/2001/XMLSchema-->
>     <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="state" 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:element name="date" type="xsd:string"/>
>     													<xsd:element name="amount" type="xsd:decimal"/>
>     												</xsd:sequence>
>     											</xsd:complexType>
>     										</xsd:element>
>     									</xsd:sequence>
>     								</xsd:complexType>
>     							</xsd:element>
>     						</xsd:sequence>
>     					</xsd:complexType>
>     				</xsd:element>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     </xsd:schema>

### Ex 5: Step 2. Open Source and Target Schemas

1. In the Data View Builder, choose File —> New Project to clear your desktop and reset all default values.
2. On the Builder Toolbar Design —> Sources tab, click Relational Databases and open two data sources:

- Double-click on the `PB-WL` (Wireless) relational database to open the schema for this data source.
- Double-click on the `PB-BB` (BroadBand) relational database to open the schema for this data source.

3. Choose the menu option File —> Set Target Schema.
4. Navigate to the Liquid Data Samples Server repository. Choose `unionOrders.xsd` and click Open.

`unionOrders.xsd` appears as the target schema.

5. Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

### Ex 5: Step 3. Clone the Orders Element of the Target Schema

1. In the Data View Builder, select the Orders element of the target schema and right-mouse click. The Orders element is a child of the Customers element and has a child called Order.
2. Choose Clone from the right-mouse menu.

The Cloned complex element labeled `Orders(2)` appears. The name of the original complex element is changed to `Orders(1)`.

### Ex 5: Step 4. Create a Query Parameter for a Customer ID

Create a Query Parameter variable, customer_id, that you can use to insert as a variable for a BroadBand customer ID value when the query runs. To create this parameter, do the following:

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 `customer_id` and click Create.

The new parameter is displayed in the Query Parameters tree.

### Ex 5: Step 5. Assign a Query Parameters

Assign the query parameter `customer_id` to the BroadBand customer ID by dragging and dropping query parameter `customer_id` to the `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` element.

### Ex 5: Step 6. Define Source Relationships

1. Within PB-BB, join the BroadBand Customer ID to the Order Customer ID.

Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto BroadBand `[PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID`

2. Join the BroadBand customer ID from the BroadBand Customer table with the Wireless customer ID from the Wireless Customer Order table as follows:

Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto `[PB-WL]/db/CUSTOMER_ORDER/CUSTOMER_ID`

### Ex 5: Step 7. Project the Output to the Target Schema

1. Project the BroadBand order information.

- Drag and drop `[PB-BB]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT` onto `[UnionOrders.xsd]/customers/customer/orders(1)/order/amount`
- Drag and drop `[PB-BB]/db/CUSTOMER_ORDER/ORDER_DATE` onto `[UnionOrders.xsd]/customers/customer/orders(1)/order/date`

2. Project the Wireless [PB-WL] order information.

- Drag and drop `[PB-WL]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT` onto `[UnionOrders.xsd]/customers/customer/orders(2)/order/amount`
- Drag and drop `[PB-WL]/db/CUSTOMER_ORDER/ORDER_DATE` onto `[UnionOrders.xsd]/customers/customer/orders(2)/order/date`

3. Project the BroadBand user information.

- Drag and drop `[PB-BB]/db/CUSTOMER/FIRST_NAME` onto `[unionOrders.xsd]/customers/customer/first_name`
- Drag and drop `[PB-BB]/db/CUSTOMER/LAST_NAME` onto `[unionOrders.xsd]/customers/customer/last_name`
- Drag and drop `[PB-BB]/db/CUSTOMER/STATE` onto `[unionOrders.xsd]/customers/customer/state`

### Ex 5: Step 8. View, then Run the Query

1. Click on the Test tab.

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

Listing 2-12 XQuery for Example 4: Union

>     <customers>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	where ($#customer_id of type xs:string  eq  $PB_BB.CUSTOMER_1/CUSTOMER_ID)
>     	return
>     	<customer>
>     		<orders>
>     			{
>     			for $PB_BB.CUSTOMER_ORDER_6 in document("PB-BB")/db/CUSTOMER_ORDER
>     			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_6/CUSTOMER_ID)
>     			return
>     			<order>
>     				<date>{ xf:data($PB_BB.CUSTOMER_ORDER_6/ORDER_DATE) }</date>
>     				<amount>{ xf:data($PB_BB.CUSTOMER_ORDER_6/TOTAL_ORDER_AMOUNT) }</amount>
>     			</order>
>     			}
>     		</orders>
>     		<orders>
>     			{
>     			for $PB_WL.CUSTOMER_ORDER_9 in document("PB-WL")/db/CUSTOMER_ORDER
>     			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_9/CUSTOMER_ID)
>     			return
>     			<order>
>     				<date>{ xf:data($PB_WL.CUSTOMER_ORDER_9/ORDER_DATE) }</date>
>     				<amount>{ xf:data($PB_WL.CUSTOMER_ORDER_9/TOTAL_ORDER_AMOUNT) }</amount>
>     			</order>
>     			}
>     		</orders>
>     		<custID>{ xf:data($PB_BB.CUSTOMER_1/CUSTOMER_ID) }</custID>
>     	</customer>
>     	}

>     </customers>

1. In the Query Parameter panel, click into the cell under "Value" and enter a value for `customer_id`. (CUSTOMER_1 through CUSTOMER_10 are available to try.)
2. Click the Run query arrow to run the query against the data sources.

### Ex 5: Step 9. Verify the Result

Querying these data sources as described in this example produces an XML query result similar to that shown in the following code listing where CUSTOMER_4 was used as the query parameter value for `customer_id`.

Listing 2-13 Result for Example 4: Union

>     <customers>
>       <customer>
>         <first_name>JOHN_B_4</first_name>
>         <last_name>KAY_4</last_name>
>         <state>NV</state>
>         <orders>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>1000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>1500</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>2000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>2500</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>3000</amount>
>           </order>
>         </orders>
>         <orders>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>1000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>2000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>4000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>5000</amount>
>           </order>
>           <order>
>             <date>2002-03-06-08:00</date>
>             <amount>10000</amount>
>           </order>
>         </orders>
>       </customer>
>     </customers>

# Example 6: Minus

A minus relationship (A minus B) returns all instances of some named value that are in A but not in B. There is no explicit minus operation in the XQuery language or Data View Builder; however, a simple compare and count technique can be used. For example: for each instance of the named value in A, count all matching instances in B; if the count is zero, that means there are no matches, and the query therefore returns the instance from A.

## The Problem

Find all customers that are BroadBand customers, but not Wireless customers. Assume that Customer IDs match across databases.

The shaded area in Figure 2-8 represents the BroadBand customers who are not Wireless customers.

Figure 2-8 BroadBand and Wireless Customers

## The Solution

If a customer has only a BroadBand account, then a join across the BroadBand and Wireless databases on that Customer ID produces an empty result. We can take advantage of that fact by counting the number of instances produced by the join. If the number is zero, then the Customer ID represents a BroadBand-only customer.

To create the solution, follow these steps:

- View a Demo
- Ex 6: Step 1. Verify the Target Schema is Saved in Repository
- Ex 6: Step 2. Open Source and Target Schemas
- Ex 6: Step 3. Find BroadBand and Wireless Customers with the Same Customer ID
- Ex 6: Step 4. Find the Count of the Wireless Customers
- Ex 6: Step 5. Set a Condition that Specifies the Output of "count" is Zero
- Ex 6: Step 6. View the XQuery and Run the Query to Test it
- Ex 6: Step 7. Verify the Result

### View a Demo

Minus 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 Liquid Data Samples Server repository.

### Ex 6: Step 1. Verify the Target Schema is Saved in Repository

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

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/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 2-14 XML Source for minus.xsd Target Schema File

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

>     <xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
>     	<xsd:element name="results">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element name="CUSTOMER" minOccurs="1" maxOccurs="unbounded">
>     					<xsd:complexType>
>     						<xsd:sequence>
>     							<xsd:element name="FIRST_NAME" type="xsd:string"/>
>     							<xsd:element name="LAST_NAME" type="xsd:string"/>
>     						</xsd:sequence>
>     					</xsd:complexType>
>     				</xsd:element>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     </xsd:schema>

### Ex 6: Step 2. Open Source and Target Schemas

1. In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.
2. On the Builder Toolbar Design—>Sources tab, click Relational Databases and open two data sources:

- Double-click on the `PB-WL` (Wireless) relational database to open the schema for this data source.
- Double-click on the `PB-BB` (BroadBand) relational database to open the schema for this data source.

3. Choose the menu option File —> Set Target Schema.

Navigate to the Liquid Data Samples Server repository or to the location where you saved the `minus.xsd` schema. Choose `minus.xsd` and click Open.

`minus.xsd` appears as the target schema.

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

4. Click the plus (+) sign (or right-mouse click and choose Expand) to expand the nodes in each source schema and in the target schema.

### Ex 6: Step 3. Find BroadBand and Wireless Customers with the Same Customer ID

- Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto `[PB-WL]/db/CUSTOMER/CUSTOMER_ID` to join the BroadBand CUSTOMER_ID and the Wireless CUSTOMER_ID.

### Ex 6: Step 4. Find the Count of the Wireless Customers

1. On the Builder Toolbar Design —> Toolbox tab, click XQuery Functions and double-click on the `xf:count` function (under Aggregate functions) to open it.
2. Drag and drop the `[PB-WL]/db/CUSTOMER/CUSTOMER_ID` onto the input of the `xf:count` function.

### Ex 6: Step 5. Set a Condition that Specifies the Output of "count" is Zero

1. Click on the Conditions tab.
2. Drag and drop the `eq` (equal) function (in the XQuery functions Comparison operators folder) onto the next empty row under Conditions on the Conditions tab.

The Functions Editor is displayed.

3. For the first parameter, drop `xf:count/result` onto `anyValue1`.
4. For the second parameter, create a Number constant, set it to `0` and drop it on `anyValue2`.

Note: To create the Number constant, on Builder —> Toolbox tab, click Constants, enter 0 in the Number field, and drag the Constant icon next to that field onto `anyValue2` in the equation in the Functions Editor.

The equality condition should look like this:

>     ([xf:count]/result eq 0)

Close the Functions Editor.

5. Project the BroadBand customers to the target results.

- Drag and drop `[PB-BB]/db/CUSTOMER/FIRST_NAME` onto `[minus.xsd]/results/CUSTOMER/FIRST_NAME`
- Drag and drop `[PB-BB]/db/CUSTOMER/LAST_NAME` onto `[minus.xsd]/results/CUSTOMER/LAST_NAME`

### Ex 6: Step 6. 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 2-15 XQuery for Example 5: Minus

>     <results>
>     	{
>     	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
>     	let $srcval_2 :=
>     	               for $PB_WL.CUSTOMER_3 in document("PB-WL")/db/CUSTOMER
>     	               where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_3/CUSTOMER_ID)
>     	               return
>     	               xf:data($PB_WL.CUSTOMER_3/CUSTOMER_ID)
>     	let $xf:count_4 := xf:count($srcval_2)
>     	where ($xf:count_4  eq  0)
>     	return
>     	<CUSTOMER>
>     		<FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_1/FIRST_NAME) }</FIRST_NAME>
>     		<LAST_NAME>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</LAST_NAME>
>     	</CUSTOMER>
>     	}
>     </results>

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

### Ex 6: Step 7. Verify the Result

When you run this query on the sample data sources as described here, the result will be one record because the sample BroadBand data source has one customer record that is different from the Wireless customer records.

Listing 2-16 Result for Example 5: Minus

>     <results>
>       <CUSTOMER>
>         <FIRST_NAME>JOHN</FIRST_NAME>
>         <LAST_NAME>PARKER</LAST_NAME>
>       </CUSTOMER>
>     </results>

# Example 7: Complex Parameter Type (CPT)

The Complex Parameter Type Cookbook example shows how to use Liquid Data to create an integrated view that connects two enterprise information systems: a database and an in-flight XML data source using a complex parameter type (CPT). A query that uses both data sources determines whether the customer has sufficient credit for the incoming order to be processed.

## The Problem

The company receives dozens of electronically transmitted orders daily and needs to quickly respond to its field office if an order cannot be accepted because a customer has exceeded their credit limit. The credit limit and amount of outstanding orders is known to the system. The quantity and price of the items being ordered is supplied in real-time along with the order.

## The Solution

The company develops a complex parameter type (CPT) that models the incoming purchase order as an XML schema and sets a simple `orderLimit` parameter that an operator can change whenever the query is run. The query also calculates the total amount outstanding of current orders and the total amount of the incoming order. The objective is to accept orders if the total amount of both outstanding and incoming orders is within the order limit. Otherwise, the order is rejected.

To recreate the solution, follow these steps:

- View a Demo
- Ex 7: Step 1. Verify the Availability of Schemas and Sample Data Stream
- Ex 7: Step 2. Open the Target Schema and CO-CPTSAMPLE CPT
- Ex: 7: Step 3. Create an orderLimit Query Parameter
- Ex 7: Step 4. Save the Project
- Ex 7: Step 5. Test Access to the Complex Parameter Source
- Ex 7: Step 6: Determine the Total Amount of New Orders
- Ex 7: Step 7. Create the Necessary Joins and Mappings to the Target Schema
- Ex 7: Step 8. Determine the Amount of Currently Open Orders
- Ex 7: Step 9: Determine the Total Amount of All Open and New Orders
- Ex 7: Step 10: Test If Open Orders + New Orders Exceeds the Order Limit
- Ex 7: Step 11: Determine If the Order is Accepted or Rejected
- Ex 7: Step 12: View the XQuery
- Ex 7: Step 13. Run the XQuery to Verify the Result

Note: The implementation details of the Complex Parameter Type demo, the DB-COCPT sample, and the CPT cookbook example vary slightly.

### View a Demo

Complex Parameter Type (CPT) 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.

### Ex 7: Step 1. Verify the Availability of Schemas and Sample Data Stream

In creating the DB-CPTCO sample query, we use the following files that are installed with Liquid Data samples.

From the Liquid Data Samples Server repository schema directory:

- BroadBand database schema. The path to this file is:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/broadbandp.sql

- Complex parameter type schema. The path to this file is:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/coCptSample2.xsd

- Target schema. The path to this file is:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/COCPTSampleTarget-Schema.xsd

- CPT sample XML stream. The path to this file is:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/coCPTsample2.xml

If you want to refer to the sample DB-CPTCO project, it is installed as the following file:

>     <WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-cptco/coCPTSample.qpr

For reference purposes, code listings for several of the XML files used in this example appear below:

Listing 2-17 DB-CPTCO Sample CPT Schema (`coCptSample2.xsd`)

>     <?xml version="1.0" encoding="UTF-8"?>
>     <xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt"
>     								xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
>     								xmlns:xsd="http://www.w3.org/2001/XMLSchema">
>     	<xsd:element name="CustOrder">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
>     					<xsd:complexType>
>     						<xsd:sequence>
>     							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
>     							<xsd:element name=
>     											"NEW_ORDER_LINE_ITEM"type="cocpt:NEW_ORDER_LINE_ITEMType"
>     											minOccurs="0" maxOccurs="unbounded"/>
>     						</xsd:sequence>
>     					</xsd:complexType>
>     				</xsd:element>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
>     		<xsd:sequence>
>     			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
>     			<xsd:element name="QUANTITY" type="xsd:decimal"/>
>     			<xsd:element name="PRICE" type="xsd:decimal"/>
>     		</xsd:sequence>
>     	</xsd:complexType>
>     </xsd:schema>

Listing 2-18 DB-CPTCO Target Schema (`COCPTSampleTargetSchema.xsd`)

>     <?xml version="1.0" encoding="UTF-8"?>
>     <xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt" xmlns:cocpt="urn:schemas-bea-com:ld-cocpt" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
>     	<xsd:element name="CustOrder">
>     		<xsd:complexType>
>     			<xsd:sequence>
>     				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
>     					<xsd:complexType>
>     						<xsd:sequence>
>     							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
>     							<xsd:element name="NEW_ORDER_LINE_ITEM" type="cocpt:NEW_ORDER_LINE_ITEMType" minOccurs="0" maxOccurs="unbounded"/>
>     						</xsd:sequence>
>     					</xsd:complexType>
>     				</xsd:element>
>     			</xsd:sequence>
>     		</xsd:complexType>
>     	</xsd:element>
>     	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
>     		<xsd:sequence>
>     			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
>     			<xsd:element name="QUANTITY" type="xsd:decimal"/>
>     			<xsd:element name="PRICE" type="xsd:decimal"/>
>     		</xsd:sequence>
>     	</xsd:complexType>
>     </xsd:schema>

Listing 2-19 DB-CPTCO Sample XML Data Stream (`coCptSample2.xml`)

>     <?xml version="1.0" encoding="UTF-8"?>
>     <cocpt:CustOrder xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
>     	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
>     	xsi:schemaLocation="urn:schemas-bea-com:ld-cocpt
>     		coCptSample2.xsd">
>     	<CUSTOMER_ORDER>
>     		<CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
>     		<NEW_ORDER_LINE_ITEM>
>     			<PRODUCT_NAME>RBBC01</PRODUCT_NAME>
>     			<QUANTITY>1000</QUANTITY>
>     			<PRICE>20</PRICE>
>     		</NEW_ORDER_LINE_ITEM>
>     		<NEW_ORDER_LINE_ITEM>
>     			<PRODUCT_NAME>CS2610</PRODUCT_NAME>
>     			<QUANTITY>1000</QUANTITY>
>     			<PRICE>20</PRICE>
>     		</NEW_ORDER_LINE_ITEM>
>     	</CUSTOMER_ORDER>
>     </cocpt:CustOrder>

You may also want to examine the CO-CPTSAMPLE definition in the WLS Administration Console for the Samples server you are using.

1. Login to the Administration Server. See Start the WLS Administration Console in the Getting Started guide for details.
2. In the left pane, click the Liquid Data node.
3. In the right pane, click the Complex Parameter Types tab and click on CO-CPTSAMPLE.

See the section Creating a Complex Parameter Type for details.

### Ex 7: Step 2. Open the Target Schema and CO-CPTSAMPLE CPT

1. In the Data View Builder, choose `File—>New Project`.
2. On the Design tab, on the Builder Toolbar, click the Toolbox tab, then click Complex Parameter Type. The `CO-CPTSAMPLE` complex parameter type is listed.
3. Double-click on `CO-CPTSAMPLE` to open the CPT schema.
4. Choose `File -> Set Target Schema`. Browse to the Liquid Data Samples Server repository schema directory.
5. Select the following file as the target schema:

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/COCPTSampleTargetSchema.xsd

In the target schema window on the right side of the design area, right-click on the top element expand the target schema.

### Ex: 7: Step 3. Create an orderLimit Query Parameter

Since credit limits vary from customer to customer, it is convenient to have an order limit query parameter that can be changed whenever a query is run.

1. In the Data View Builder select `Design—>Toolbox`.
2. Click on the Query Parameter tab.
3. Enter `orderLimit` as a parameter name.
4. Select `xs:decimal` as the parameter type.
5. Click Create.
6. Drag and drop the `orderLimit` parameter icon onto the target schema `[COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/TOTAL_OPEN_ORDERLIMIT`

### Ex 7: Step 4. Save the Project

You can save a project at any time. To initially create a project, use `File—>Save Project As`. Use the file browser to choose a location and project name (we use `myCoCPT`).

### Ex 7: Step 5. Test Access to the Complex Parameter Source

Follow these steps to verify access to the CPT data source:

1. Drag and drop output from:

>     [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/CUSTOMER_ID

onto the target schema:

>     [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/cocpt:CUSTOMER/cocpt:CUSTOMER_ID.

2. Click the Test tab.
3. In the lower-left pane of the Data View Builder (Test mode), click in the Values area under Query Parameters to the right of the CPT name (`CO-CPTSAMPLE`).
4. Navigate to the XML data file associated with the CO-CPTSAMPLE complex parameter type.

>     <WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/coCptSample2.xml

5. Enter an `orderLimit` value such as `200000`.
6. Now you can click the Run arrow to execute a preliminary query. The following result shows that your CPT is successfully retrieving from the XML file data:

Listing 2-20 Interim Results (1) from the CPT Example Query

>     <prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
>       <prefix1:CUSTOMER>
>         <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
>         <prefix1:CUSTOMER_ORDER>
>           <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
>         </prefix1:CUSTOMER_ORDER>
>       </prefix1:CUSTOMER>
>     </prefix1:CustOrderStatus>

1. Return to the Toolbar Design mode.

In the case of this data source, the customer identification is provided so there is no need to create a customer ID query parameter.

### Ex 7: Step 6: Determine the Total Amount of New Orders

Since all runtime source items from a CPT are treated as character strings, any data items from the CO-CPTSAMPLE data source must first be cast appropriately. Then quantities and prices are multiplied together. The sum of the products of quantity and prices is the total amount of new orders.

1. Click XQuery Functions.
2. Drag and drop a multiply [`*]` function into the design area (from the Numeric operators group).
3. Drag and drop a sum Aggregate function (xf:sum) into the design area.

Figure 2-9 XQuery Functions Used to Calculate Total New Orders in Data Stream

4. Drag and drop the output result of `xs:decimal` to one side of the multiply equation and the output result of `xs:decimal2` to the other.
5. Drag and drop `[CO-CPTSAMPLE]cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/QUANTITY` to one side of the multiply equation.
6. Drag and drop `[CO-CPTSAMPLE]cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/PRICE` to the other side of the multiply equation.
7. Drag the [`*`]`function` output result to the input parameter of `xf:sum`. This gives you the total order amount in the CPT data source.
8. Drag and drop output from `xf:sum` onto the target schema `[COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/NEW_ORDER_TOTAL_AMOUNT`
9. Rerun your query. A new order total of `40000` appears.
10. Return to Design mode.

### Ex 7: Step 7. Create the Necessary Joins and Mappings to the Target Schema

Move the PB-BB relational source schema onto the design area.

1. On the Builder Toolbar Design—>Sources tab, click Relational Databases, and double-click on `PB-BB` to open the schema for the BroadBand sample data source.
2. Expand the `PB-BB` schema.

Create the necessary joins to allow us to fetch the line items for a particular order for a particular customer.

1. Drag and drop `[CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/CUSTOMER_ID` onto `[PB-BB]/db/CUSTOMER/CUSTOMER_ID`
2. Drag and drop `[PB-BB]/db/CUSTOMER/CUSTOMER_ID` onto the `[PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID`
3. Drag and drop `[PB-BB]/db/CUSTOMER_ORDER/ORDER_ID` onto `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID`

Figure 2-10 Setting Joins Between CPT and Relational Data Source

Next, project `FIRST_NAME` and `LAST_NAME` elements onto the target schema.

1. Drag and drop `[PB-BB]/db/CUSTOMER/FIRST_NAME` onto the target schema `[cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/FIRST_NAME`
2. Drag and drop `[PB-BB]/db/CUSTOMER/LAST_NAME` onto the target schema `[cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/LAST_NAME`

Note: In this version of the CO-CPTSAMPLE, automatic scoping is used. See Understanding Condition Scoping in Building Queries and Data Views for more information.

Although your query is not complete, you can test run it again.

Listing 2-21 Interim Results (2) from CPT Example Query

>     <prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
>       <prefix1:CUSTOMER>
>         <prefix1:FIRST_NAME>JOHN_B_1</prefix1:FIRST_NAME>
>         <prefix1:LAST_NAME>KAY_1</prefix1:LAST_NAME>
>         <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
>         <prefix1:CUSTOMER_ORDER>
>           <prefix1:NEWORDER_TOTAL_AMOUNT>40000</prefix1:NEWORDER_TOTAL_AMOUNT>
>           <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
>         </prefix1:CUSTOMER_ORDER>
>       </prefix1:CUSTOMER>
>     </prefix1:CustOrderStatus>

1. Return to Design mode.

### Ex 7: Step 8. Determine the Amount of Currently Open Orders

Follow these steps to find the total amount of open orders in the sample PB-BB database:

1. Click the XQuery Functions.
2. Drag and drop the multiply [`*`] operator into the design area.
3. Drag and drop `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/QUANTITY` and `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/PRICE` to the function operands.
4. Drag and drop another `xf:sum` into the design area. Drag the output result of the `f()*2 - Function` (`*2` being the second use of a multiplication function) to the input parameter of `xf:sum2`.
5. Drag and drop output from `xf:sum2` onto the target schema `[COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/OPEN_ORDER_TOTAL_AMOUNT`

Finally, we need to restrict results to open orders:

1. In the Data View Builder select Design —> Toolbox.
2. Click on Constants.
3. Create a string constant called `OPEN` and drag the icon to the right of `OPEN` to the `[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS` item.

If we run the query, the amount of open orders should be `150000`.

### Ex 7: Step 9: Determine the Total Amount of All Open and New Orders

1. Click XQuery Functions.
2. Drag and drop the plus [`+`] numeric operator into the design area.
3. Use `xf:sum` (total new orders) and `xf:sum2` (total open orders) as the operands to obtain the total of open and new order amount.

### Ex 7: Step 10: Test If Open Orders + New Orders Exceeds the Order Limit

1. Select XQuery Functions.
2. Under Comparison operators locate the [`gt`] (greater than) function. Drag it into the design area.
3. Use the output of the sum of open and newly arrived orders as the first input parameter.
4. Use the query parameter `orderLimit` as input for `operand2`. The result is a Boolean value that is True if the sum of open and newly arrived orders is greater than the overall order limit.

### Ex 7: Step 11: Determine If the Order is Accepted or Rejected

Now that the relationships and conditions are established, set up an if-then-else test to solve the business problem. See The Problem.

1. Under Other functions locate the `xfext:if-then-else` function. Drag it into the design area.
2. Drag the output of the `gt` function to the `if-then-else` condition parameter.
3. Click on the Toolbox `Constants` tab. In the String field enter `REJECT`, drag the String field icon to the `then` parameter in the `xfext:if-then-else` function.
4. Change the String field to `ACCEPT`, then drag the String field icon to the `else` parameter.
5. Drag the output result onto the target schema `[COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/ORDER_REVIEW_STATUS`
6. Click Test.

### Ex 7: Step 12: View the XQuery

The generated XQuery is shown in the following code listing.

Listing 2-22 XQuery for Example 6: Complex Parameter Type (CPT)

>     namespace cocpt = "urn:schemas-bea-com:ld-cocpt"

>     <cocpt:CustOrderStatus>
>     	{
>     	for $COCPTSAMPLE.CUSTOMER_ORDER_2 in ($#COCPTSAMPLE of type element cocpt:CustOrder)/CUSTOMER_ORDER
>     	for $PB_BB.CUSTOMER_3 in document("PB-BB")/db/CUSTOMER
>     	let $srcval_4 :=
>     	               for $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5 in document("PB-BB")/db/CUSTOMER_ORDER_LINE_ITEM
>     	               where xf:not(xf:empty(
>     	               	for $PB_BB.CUSTOMER_ORDER_7 in document("PB-BB")/db/CUSTOMER_ORDER
>     	               	where ($PB_BB.CUSTOMER_3/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_ORDER_7/CUSTOMER_ID)
>     	               	  and ($PB_BB.CUSTOMER_ORDER_7/ORDER_ID  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/ORDER_ID)
>     	               	return
>     	               	xf:true()))
>     	                 and ("OPEN"  eq  $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/STATUS)
>     	               return
>     	               $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/QUANTITY * $PB_BB.CUSTOMER_ORDER_LINE_ITEM_5/PRICE
>     	let $xf:sum2_8 := xf:sum($srcval_4)
>     	let $srcval_9 :=
>     	               for $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10 in $COCPTSAMPLE.CUSTOMER_ORDER_2/NEW_ORDER_LINE_ITEM
>     	               return
>     	               $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10/QUANTITY * $COCPTSAMPLE.NEW_ORDER_LINE_ITEM_10/PRICE
>     	let $xf:sum_12 := xf:sum($srcval_9)
>     	let $v_13 := $xf:sum2_8 + $xf:sum_12
>     	let $gt_14 := $v_13 gt $#orderLimit of type xs:decimal
>     	where ($COCPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID  eq  $PB_BB.CUSTOMER_3/CUSTOMER_ID)
>     	return
>     	<cocpt:CUSTOMER>
>     		<cocpt:FIRST_NAME>{ xf:data($PB_BB.CUSTOMER_3/FIRST_NAME) }</cocpt:FIRST_NAME>
>     		<cocpt:LAST_NAME>{ xf:data($PB_BB.CUSTOMER_3/LAST_NAME) }</cocpt:LAST_NAME>
>     		<cocpt:CUSTOMER_ID>{ xf:data($COCPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID) }</cocpt:CUSTOMER_ID>
>     		<cocpt:CUSTOMER_ORDER>
>     			<cocpt:OPENORDER_TOTAL_AMOUNT>{ $xf:sum2_8 }</cocpt:OPENORDER_TOTAL_AMOUNT>
>     			<cocpt:NEWORDER_TOTAL_AMOUNT>{ $xf:sum_12 }</cocpt:NEWORDER_TOTAL_AMOUNT>
>     			<cocpt:TOTAL_OPEN_ORDERLIMIT>{ $#orderLimit of type xs:decimal }</cocpt:TOTAL_OPEN_ORDERLIMIT>
>     			<cocpt:ORDER_REVIEW_STATUS>{ xfext:if-then-else( treat as xs:boolean($gt_14), "REJECT", "ACCEPT") }</cocpt:ORDER_REVIEW_STATUS>
>     		</cocpt:CUSTOMER_ORDER>
>     	</cocpt:CUSTOMER>
>     	}
>     </cocpt:CustOrderStatus>

1. Return to Design mode.

### Ex 7: Step 13. Run the XQuery to Verify the Result

When you run this query on the sample data sources as described in this example, the result is an accepted order.

Listing 2-23 Result of Example 6: Complex Parameter Type (CPT)

>     <prefix1:CustOrderStatus xmlns:prefix1="urn:schemas-bea-com:ld-cocpt">
>       <prefix1:CUSTOMER>
>         <prefix1:FIRST_NAME>JOHN_B_1</prefix1:FIRST_NAME>
>         <prefix1:LAST_NAME>KAY_1</prefix1:LAST_NAME>
>         <prefix1:CUSTOMER_ID>CUSTOMER_1</prefix1:CUSTOMER_ID>
>         <prefix1:CUSTOMER_ORDER>
>           <prefix1:OPENORDER_TOTAL_AMOUNT>150000</prefix1:OPENORDER_TOTAL_AMOUNT>
>           <prefix1:NEWORDER_TOTAL_AMOUNT>40000</prefix1:NEWORDER_TOTAL_AMOUNT>
>           <prefix1:TOTAL_OPEN_ORDERLIMIT>200000</prefix1:TOTAL_OPEN_ORDERLIMIT>
>           <prefix1:ORDER_REVIEW_STATUS>ACCEPT</prefix1:ORDER_REVIEW_STATUS>
>         </prefix1:CUSTOMER_ORDER>
>       </prefix1:CUSTOMER>
>     </prefix1:CustOrderStatus>