Example 5: 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  9-4 represents the Broadband customers who are not Wireless customers.

Figure 9-4 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 5: Step 1. Verify the Target Schema is Saved in Repository
• Ex 5: Step 2. Open Source and Target Schemas
• Ex 5: Step 3. Find Broadband and Wireless Customers with the Same Customer  ID
• Ex 5: Step 4. Find the Count of the Wireless Customers
• Ex 5: Step 5. Set a Condition that Specifies the Output of "count" is Zero
• Ex 5: Step 6. View the XQuery and Run the Query to Test it
• Ex 5: 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 server Repository.

Ex 5: 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 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-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 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.

   Navigate to the 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 5: 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 5: Step 4. Find the Count of the Wireless Customers

1. On the Builder Toolbar Design—>Toolbox tab, click 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 5: 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 [count-Function:Output]/Parameters/integer 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 5: 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 9-15 XQuery for Example 5: Minus

{--	Generated by Data View Builder 1.1	--}
<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>

 

---

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

Ex 5: 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 9-16 Result for Example 5: Minus

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