Example 4: 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 4: Step 1. Verify the Target Schema is Saved in Repository
• Ex 4: Step 2. Open Source and Target Schemas
• Ex 4: Step 3. Clone the Orders Element of the Target Schema
• Ex 4: Step 4. Create a Query Parameter for a Customer ID
• Ex 4: Step 5. Assign a Query Parameters
• Ex 4: Step 6. Define Source Relationships
• Ex 4: Step 7. Project the Output to the Target Schema
• Ex 4: Step 8. Add Optimization Hints
• Ex 4: Step 9. View the XQuery and Run the Query to Test it
• Ex 4: Step 10. 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 server Repository.

Ex 4: 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 Samples server repository. The path to the schemas folder in the Liquid Data server repository is:

ld_repository/schemas/

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

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-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 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 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 server Repository. Choose unionOrders.xsd and click Open.

   unionOrders.xsd appears as the target schema.

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

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 4: 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 element labeled Orders(2) appears.

Ex 4: 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 Add.

   The new parameter is displayed in the Query Parameters tree.

Ex 4: Step 5. Assign a Query Parameters

• Assign the query parameter customer_id to the Broadband customer ID as follows:

Drag and drop query parameter customer_id to the [PB-BB]/db/CUSTOMER/CUSTOMER_ID node.

Ex 4: 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 4: 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 4: Step 8. Add Optimization Hints

Because you know that the Customer table is much smaller than the Customer Orders table, you can add optimization hints to improve query performance.

1. Click the Optimize tab in the Data View Builder.

2. On the Join Pair Hints panel, choose All from the drop-down list to display all of the join conditions.

3. For both of the join pairs, select Pass Parameter to Right. You pass the parameter to the right because the right table (the Customer Orders table) is much larger than the left table (the Customer table).

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 9-12 XQuery for Example 4: Union

{--	Generated by Data View Builder 1.1	--}
<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>
		<state>{ xf:data($PB_BB.CUSTOMER_1/STATE) }</state>
		<orders>
			{
			for $PB_BB.CUSTOMER_ORDER_2 in document("PB-BB")/db/CUSTOMER_ORDER
			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  {--! ppright !--}
					$PB_BB.CUSTOMER_ORDER_2/CUSTOMER_ID)
			return
			<order>
				<date>{ cast as xs:string(xf:data($PB_BB.CUSTOMER_ORDER_2/ORDER_DATE))
						}</date>
				<amount>{ xf:data($PB_BB.CUSTOMER_ORDER_2/TOTAL_ORDER_AMOUNT) }</amount>
			</order>
			}
		</orders>
		<orders>
			{
			for $PB_WL.CUSTOMER_ORDER_3 in document("PB-WL")/db/CUSTOMER_ORDER
			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  {--! ppright !--}
					$PB_WL.CUSTOMER_ORDER_3/CUSTOMER_ID)
			return
			<order>
				<date>{ cast as xs:string(xf:data($PB_WL.CUSTOMER_ORDER_3/ORDER_DATE))
					}</date>
				<amount>{ xf:data($PB_WL.CUSTOMER_ORDER_3/TOTAL_ORDER_AMOUNT) }</amount>
			</order>
			}
		</orders>
	</customer>
	}
</customers>

 

---

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

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

Ex 4: Step 10. 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 9-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>

 

---