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. Set Optimization Hints
• Ex 1: Step 8. View the XQuery and Run the Query to Test it
• Ex. 1: Step 9. 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 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 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-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 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 (join) [PB-WL]db/CUSTOMER/CUSTOMER_ID to [PB-BB]db/CUSTOMER_ORDER/CUSTOMER_ID.

Ex 1: Step 7. Set Optimization Hints

1. Click the Optimize tab.

2. Under Join Pair Hints, on the drop-down menu select PB-WL and PB-BB.

   This represents the first join you created between Wireless and Broadband Customer IDs.

3. Click into the empty cell under Hints to get the drop-down menu and choose "Pass Parameter to Right" for the PB-WL and PB-BB join.

Note: For information on using optimization hints see Optimization Hints for Joins.

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

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

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

 

---

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

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

Ex. 1: Step 9. Verify the Result

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

Listing 9-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>

 

---