Building Queries and Data Views

Building Queries

This chapter explains how to design and build a BEA Liquid Data for WebLogic query using the Data View Builder, including manually applying condition scoping rules. The following topics are covered:

• Defining Query Requirements
• Managing Query Components
• Working With Source and Target Schema Elements
• Setting Query Conditions
• Sorting Query Results
• Using Existential Condition Checking in Queries
• Using Automatic Type Casting

 

---

Defining Query Requirements

The first step in constructing a query (or, as often, a set of queries) is design: drawing on business requirements to answer the following questions:

• What is the description of the data integration problem to be solved?
• How do I want the query result to look? In other words, how do I want to structure the output?
• What types of data sources does my query need?
• What is the structure of each data source; that is, what are the input (if any) and output XML schemas for the source?
• How does source data map to the target?
• What conditions do I need to define? (Conditions filter source data in a specific way.)
• What target XML schema design pattern should I use? More specifically, what is the appropriate cardinality of each element in the target schema? Proper design of the target XML schema is a key factor in building a successful and efficient query.

Once you have designed the query and defined an outline strategy for accomplishing the information mapping and filtering, you are ready to build a test version of your query. For other than very simple queries, you will probably revise, refine and test the query several times.

Examples Set-up

This chapter contains several illustrated, stepped examples. If you want to work through these examples in the Data View Builder, you can easily do so.

Unless otherwise indicated each example requires the following set-up instructions.

Move schemas for the following two data sources into the Data View Builder work area:

• Relational Database: PB-WL
• XML File: XM-BB-C

With the Data View Builder open click the Design tab.
On the Builder Toolbar, click the Sources tab (on the bottom of the left vertical panel).
• Click Relational Databases and then double-click on PB-WL data source to open the associated XML schema showing Wireless customers.
• Click XML Files and then double-click on XM-BB-C data source to open the associated XML schema showing BroadBand customers.

The schemas for each of the data sources appear.

Position the schema windows so you can view the data elements in each schema. You can expand the data elements by clicking the plus [+] sign next to the element name. For example, in the PB-WL data source, CUSTOMER is a complex element with subordinate simple elements.

Figure 5-1 Example with Data Sources Expanded

To create and set the target schema for this example cut-and-paste the XML in Listing 5-1 into a plain text file and save it to the Liquid Data Server repository under the file name:

amtByState.xsd

The path to the schemas folder in the Liquid Data Server repository is:

<ldrepository>/schemas

An example of a full path to the Liquid Data Server repository is:

/bea81/weblogic81/samples/domains/liquiddata/ldrepository

Listing 5-1 XML source for amtByState.xsd target schema

<?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="STATE" minOccurs="0" maxOccurs="unbounded">
     <xsd:complexType>
      <xsd:sequence>
       <xsd:element name="state" type="xsd:string" minOccurs="0" maxOccurs="1"/>
      </xsd:sequence>
     </xsd:complexType>
    </xsd:element>
    <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="AVERAGE_ORDER" type="xsd:string"/>
       <xsd:element name="CUSTOMER_ID" type="xsd:string"/>
       <xsd:element name="STATE" type="xsd:string"/>
       </xsd:sequence>
      </xsd:complexType>
     </xsd:element>
    </xsd:sequence>
   </xsd:complexType>
  </xsd:element>
</xsd:schema>

Navigate to the Liquid Data Server repository, the topmost directory in the browser.
Choose amtByState.xsd and click Open.

Figure 5-2 Selecting the Newly Created Schema as Target Schema




 

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

Figure 5-3 Example Showing Data Sources and Target Schema

 

Using the Function Editor

You can use the Data View Builder Function Editor to build up XQuery functions (see Using XQuery Functions for more information).

Figure 5-4 Function Editor

 

To use the Function Editor:

Drag and drop an XQuery equals function [eq] from the Toolbox panel to the first empty row in the Conditions tab.
Drag a source schema element and drop it into the same row of the Condition column. Drag a second source schema element and drop it into the same row of the Condition column.

To edit an existing function:

Open the Functions Editor by clicking the Edit button.




 
Edit the statement as needed. You will need to delete the current parameters or function using the Trashcan or Delete key. Then drag and drop a new function or source elements/attributes to the Functions Editor.

Figure 5-5 Mapping Elements to Functions




 

To get the view shown in Figure 5-5, click on the Conditions tab, select the row with the condition to be edited, then click the Edit button.

You can drag and drop different functions into the Functions Editor from the XQuery Functions panel on the Builder Toolbar —> Toolbox tab.

For more information about using the Functions Editor and working with functions see XQuery Functions and Function Editor.

 

---

Managing Query Components

If you think of selected data elements as nouns (what you want to work on), the functions as verbs (the action), then the mapping among the data elements creates a logical sentence that expresses the query.

Results and query performance can change significantly depending on how you:

• Map (or project) source data from one or more sources to the target schema
• Specify conditions (filter source data)
• Tune the target schema

Although you can simply type in an XQuery and run it from the Data View Builder, the more common way to create a query is build it up through the following operations:

• Map simple or complex elements from source schemas to target schemas
• Define constants and/or query parameters
• Create join relationships between source schema elements
• Transform information using built-in or custom functions
• Filter data using conditions

In the Data View Builder these operations can occur in any order and are fully reversible.

If you have taken the time to outline a design for the query first, constructing it will be a matter of drag-and-drop query building. Then you can test, fine-tune, and modify your project as needed to produce variations on the results, or to optimize the query for better performance.

In addition to data sources (see Data Sources), constants, query parameters, and XQuery functions are used in constructing a Liquid Data XQuery graphically.

Data Sources

A data source is represented in the Data View Builder through a source XML schema. You can use multiple data source schemas in your query. In some cases you may need to use a single source schema multiple times. Some data sources require input data as well.

Using the Sources tab on the Data View Builder Toolbar you can access available data sources, grouped by type, that are configured on the Liquid Data Server to which you are connected. Note that a data source group (such as Relational Databases) appears only if at least one source of that type has been configured in the Liquid Data Server to which you are connected.

See Liquid Data Getting Started and Administration Guide for examples of configuring and using data sources.

Creating and Using Constants

You can add constants to functions or use constants as part of any query condition.

Figure 5-6 Toolbox Constants Panel

 

To create a constant choose Toolbox —>Constants. Four options are available:

• String Constant. Strings are alphanumeric values that typically contain alphabetic letters, special characters, and digits used in non-numeric comparisons. Names, zip codes, phone numbers, and street addresses are typical examples of string values.
• Number Constant. Numbers can be integers (positive or negative), decimal values, or floating point expressions.
• Empty List (null value). Creates an empty list in the generated XQuery such as:

<CUSTOMER_ID>{ () }</CUSTOMER_ID>

• Empty Element. Creates an empty element in the generated XQuery such as:

<STATE/>

Using Constants with Functions

To include a constant as a function parameter, follow steps similar to those in the following example:

Drag the desired XQuery function to a row on the Condition tab or to the Liquid Data desktop. For example: choose the starts-with function. You get the following placeholder in the Functions Editor:

xf:starts-with(str1,str2)

Drag an appropriate source element onto the first string placeholder (str1). For example, choose CustomerID from a source schema.
Type a value in the String constant text box. For example, CellPhone. Drag the Constants icon onto the second string placeholder (str2).

The condition appears in the Functions Editor as shown in Figure 5-7.

Figure 5-7 Condition with starts-with Constant in Functions Editor




 

Close the Functions Editor by clicking the Close button. The new condition you created appears in the Source column on the Condition tab.

Figure 5-8 Condition with starts-with Constant in Row on Conditions Tab




 

Note: If you design a query with a constant, and then design another query using a query parameter that specifies exactly the same value, the generated queries will differ somewhat even though the functionality will be the same.

Creating and Using Query Parameters

Using a query parameter you can change a value in your query each time it is run. This is ideal for ad hoc queries based around changes in a customer name or order number.

The Query Parameter section of the Toolbox provides a text field where you can enter a new parameter name. To create a query parameter:

Name your query parameter.
Select the parameter type from the drop-down list (See Table 5-10).
Click Create. Your new parameter will appear in the Query Parameter resource tree (Figure 5-9).

Figure 5-9 Query Parameters Dialog Box




 

To expand the list of query parameters right-click on the Simple Types folder. You can then right-click on the query parameter name to rename or delete the parameter.

To use a simple query parameter, drag and drop a parameter name to the appropriate item of source data. Then, when you run your query, a window will appear where you can enter your test parameter.

For an example showing use of a query parameter, see the Getting Started demo:

http://download.oracle.com/docs/cd/E13190_01/liquiddata/docs81/interm/demopage.html

Using XQuery Functions

In Liquid Data, XQuery functions are a set of built-in functions that allow you to graphically establish functional relationships between data elements or to apply business logic to data.

You can double-click or drag and drop a function to move it the Liquid Data desktop. The function will appear in a structured format that displays the number and type of input parameters required, as well as the output parameter.

For most XQuery functions you drag-and-drop one or more information element to the function. The information element can be source data, variables, or constant values. Functions return results based on input and the output element with which the results are associated.

Figure 5-11 Sample XQuery Function as it Appears in the Data View Builder Work Area

 

For example, if you want to find out how many customer IDs in the BroadBand database are not equal to those in the Wireless database you can use the [ne] (not-equal-to) function.

To access this function go to Builder Toolbar —> Toolbox tab —> XQuery Functions area, expand the Operators element, and drag the [ne] function into the work area.

Figure 5-12 XQuery Functions Panel Showing Aggregate and Boolean Functions Tab Expanded

 

Note: Automatic type casting is available to help ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is active, Liquid Data verifies (and if necessary promotes) the data types of input parameters for all source-to-target mappings and functions. For more information about automatic type casting, see Using Automatic Type Casting.

Most XQuery functions in the Data View Builder are standard XQuery functions supported by the W3C. (For related information about using functions, see Functions Reference in the XQuery Reference Guide. For more detailed information, see the W3C XQuery 1.0 and XPath 2.0 Functions and Operators specification.)

Mapping Elements to Functions

When you drag and drop a source element onto another source element (either within the same source schema or among different source schemas) you are automatically creating a join which is represented in the Data View Builder as an equality relationship between the two elements/attributes using the [eq] (equals) function.

You can also create the same equality relationship by dragging and dropping the eq function onto a row in the Conditions tab and then dragging and dropping two source elements/attributes into the same row.

 

 

---

Working With Source and Target Schema Elements

Mapping schema elements involves establishing a visual relationship among data source elements, attributes, and functions and to a target schema.

There are two types of schema elements: simple and complex. Complex elements contain elements and/or attributes. Simple elements can hold content and have attributes, but do not contain other elements.

Figure 5-13 Expanded Schema Showing Complex and Simple Elements

 

 

To expand a complex element, right-click on it and choose Expand (or just double-click). If you do this for the topmost element in the schema, all the complex elements will be expanded.

Supported Drag-and-Drop Actions in the Data View Builder

The Data View Builder supports the drag-and-drop actions that are described in the following table.

Table 5-14 Supported Mapping Relationships

 

Mapping to Target Schemas

The Data View Builder automatically generates queries based on target schemas and the mappings into them. (See Liquid Data Getting Started for an example.)

The Data View Builder supports two types of mappings: value mappings and complex element mappings. Value mappings map (assign) only the value of an element or attribute from a source to the value of its target element or attribute. Element mappings allow mapping source elements (simple or complex) to target elements.

For more details on creating source and target schemas see Source and Target Schemas.

Mapping Elements and Attributes Between Source and Target Schema

Value mappings of elements and attributes allow you to map source contents to corresponding target elements.

Figure 5-15 illustrates a simple join of the source element STATE in the BroadBand source schema (XM-BB-C) with a source element STATE in the Wireless source schema (PB-WL). This action joins the common elements in each schema and disregards those that do not occur in both schemas.

To project a result, you can designate how the output of this relationship should look when the query runs. Because you are collecting only information about states and defining only one element in the target schema, you are in effect asking the Data View Builder to fill only that data element in the result when the query runs.

If you are following along in the Data View Builder, drag and drop the STATE element in PB-WL source schema onto the state? element (under STATE*) in the target schema.

Symbols next to the element name such as [*], [?], and [+] represent repeatable and optional conditions. For details see Managing Target Schema Properties.

Figure 5-15 Mapping Elements in the Data View Builder

 

Complex Element Mappings

Complex element mappings provide a quick and efficient way to copy entire sub-parts of source elements to your target schema. This is useful where parts of the target result are (or should be) identical or nearly identical to parts of the sources.

There are many situations which you will find it convenient to map elements to your target schema, including:

• When you are creating a target schema from scratch.
• When you are sure that your source schema matches your target schema in terms of both elements and attributes.
• When you want elements individually mapped but it is easier to map complex elements, expand the mappings to include values, and then add or delete some mappings using right-click target schema management commands.

There are several benefits of mapping or projecting elements:

One-to-one mapping of multiple elements is less often needed.
The query is easier to read compared to a query where individual elements are mapped.
The query runs faster, due to fewer element or attribute constructors.
If the underlying structure of the complex element changes — an element is added, deleted, or an attribute is changed — the generated query does not change.

Figure 5-16 shows the results of the mapping of a complex element to the target schema. The mapping was accomplished by:

Choosing File —> New Project in the Data View Builder. This clears any data sources, target schema or other settings.
In Design mode double-clicking on the XM-WL-CO XML data source.
Right-clicking on the complex element named CUSTOMER_ORDER.
Choosing Copy.
Right-clicking on results.
Choosing Paste and Map. Mapped elements appear in italics, indicating that the elements are mapped without values.

Figure 5-16 Mapping a Complex Element




 

When you select Test mode, an XQuery is generated that returns all the child elements of CUSTOMER_ORDER.

Listing 5-2 XQuery resulting from mapping of CUSTOMER_ORDER complex element

<results>
	{
	for $XM_WL_CO.CUSTOMER_ORDER_1 in document("XM-WL-CO")/db/CUSTOMER_ORDER
		return
		$XM_WL_CO.CUSTOMER_ORDER_1
	}
</results>

Expanding Mapped Complex Elements

Here are two examples where you might find it useful to use element mapping even when there is not an exact match between the source and target schema:

• A complex element called Customer may contain FIRST_NAME, LAST_NAME, PHONE, ADDRESS, and so on. Even if you don't want every one of these elements mapping it may be easier to map all first and then delete a few mappings.
• You may have a target schema that is close to the source, but not an exact match. In such a case it may be easier to:
• Delete the target schema element(s)
• Copy, then paste-and-map the source element(s)
• Expand the mapping to include values
• Edit the target schema as needed

Figure 5-17 shows the results of the mapping a set of simple elements to their corresponding elements in the target schema. Although this mapping could have been accomplished by drag-and-drop of each element individually, it was easier to follow the steps for mapping a complex element (Figure 5-16) and then to right-click on the complex element name and select Expand complex mapping. The results is exactly as if you had individually mapped all the simple elements from source to target schema. In this case no further editing of the target schema was done.

Figure 5-17 Mapping Simple Elements

 

When Test mode is selected a query is generated based on the value mapping of all sub-elements associated with CUSTOMER_ORDER.

Listing 5-3 XQuery resulting from the mapping of individual CUSTOMER_ORDER elements

<results>
	{
	for $XM_WL_CO.CUSTOMER_ORDER_1 in document("XM-WL-CO")/db/CUSTOMER_ORDER
	return
	<CUSTOMER_ORDER>
		<ORDER_DATE>{ xf:data($XM_WL_CO.CUSTOMER_ORDER_1/ORDER_DATE) }</ORDER_DATE>
		<ORDER_ID>{ xf:data($XM_WL_CO.CUSTOMER_ORDER_1/ORDER_ID) }</ORDER_ID>
		<CUSTOMER_ID>{ xf:data($XM_WL_CO.CUSTOMER_ORDER_1/CUSTOMER_ID) }</CUSTOMER_ID>

		<SHIP_METHOD>{ xf:data($XM_WL_CO.CUSTOMER_ORDER_1/SHIP_METHOD) }</SHIP_METHOD>
		<TOTAL_ORDER_AMOUNT>{ xf:data($XM_WL_CO.CUSTOMER_ORDER_1/TOTAL_ORDER_AMOUNT) }</TOTAL_ORDER_AMOUNT>
	</CUSTOMER_ORDER>
	}
</results>

Notes:

• You cannot clone a complex element using the Paste and Map option. Instead either rename or delete the complex element in the target schema first or map it to a different location in the schema.
• You cannot map the same element from more than one source schema to a single element in the target schema. For example, if you map STATE (under CUSTOMER) from the BroadBand database to cust_state in the target schema and then map STATE from a second source schema to cust_state in the target schema, only the latter mapping will apply.

Removing Mappings

Mapped elements/attributes in a query are displayed on the Mappings tab. You can delete mappings between elements and attributes by:

Highlighted the element or attribute row you want to delete.
Clicking the Trashcan icon or pressing the Delete key (see Figure 5-11).

Figure 5-18 Removing a Mapping




 

Modifying Target Schemas

You can make changes to a target schema by right-clicking an element. A pop-up menu displays available options.

 

Managing Target Schema Properties

Liquid Data provides for the setting of combinations of Optional and Repeatable properties on target schema elements. The Data View Builder uses these properties settings to determine the shape of the result set when generating a query.

The following modified version of the customerOrderReport sample schema has FIRST_NAME taking the default condition (no repeat, mandatory), followed by examples of elements with repeatable [+], optional [?], and optional and repeatable [*] properties.

Figure 5-19 Various Target Schema Element Attribute Settings

 

Listing 5-4 shows how these settings are rendered in the generated target XML schema:

Listing 5-4 Various element attribute settings in a generated target schema

<xsd:element name="CUSTOMER">
		<xsd:complexType>
		<xsd:sequence>
			<xsd:element name="FIRST_NAME" type="xsd:string"/>
			<xsd:element name="LAST_NAME" type="xsd:string" maxOccurs="unbounded"/>
			<xsd:element name="CUSTOMER_ID" type="xsd:string" minOccurs="0"/>
			<xsd:element name="STATE" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>
			<xsd:element name="EMAIL_ADDRESS" type="xsd:string"/>
			<xsd:element name="TELEPHONE_NUMBER" type="xsd:long"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:element>

The following table summarizes the rendering of element properties in the Data View Builder and the generated target schema.

Table 5-20 Rendering of Element Attributes in Data View Builder and Target Schema XML

Repeatable Property Settings

When you set a simple or complex element in a target schema to Repeatable (plus [+] or asterisk [*]) it means that the element can repeat within the confines of its enclosed parent in the form:

<groupA>
		<item1>
		<item2>
<groupB>
		<item1>
		<item2>
..

If the Repeatable (+ or *) attribute is not selected, then query results would appear in the form:

<groupA>
		<item1>
<groupB>
		<item1>
<groupA>
		<item2>
<groupB>
		<item2>
..

Thus the Repeatable element setting is important in maximizing the readability of your query results.

Consider the following target schema:

Figure 5-21 Target schema with a non-repeatable elements

 

In this target schema, the firstname and lastname elements are non-repeatable and the custrecord element is defined as repeatable and required. If you map data to the firstname and lastname elements, this target schema will generate results similar to the following:

<customers>
  <custrecord>
    <firstname>John</firstname>
    <lastname>Parker</lastname>
  </custrecord>
  <custrecord>
    <firstname>John</firstname>
    <lastname>Warfin</lastname>
  </custrecord>
  ..
  ..
</customers>

If you modify the target schema so the firstname and lastname elements are also repeatable (see example in Figure 5-22), the resulting schema for the generated query will be different.

Figure 5-22 Target Schema Properties Dialog with Repeatable Attribute Selected

 

With the changed target schema, the Data View Builder will now generate a query with results similar to:

<customers>
  <custrecord>
    <firstname>John</firstname>
    <firstname>John</firstname>
    ..
    ..
    <lastname>Parker</lastname>
    <lastname>Warfin</lastname>
    ..
    ..
  </custrecord>
</customers>

In this case it is likely that the query designer would want the result set to display the first and last names together for the same customer, and would therefore desire the firstname and lastname elements to be non-repeatable.

 

Optional Attribute Settings

By default target schema elements are required.

If a complex or simple element in a target schema is set to Optional, a question mark [?] or asterisk [*] appears next to its name, meaning that the element can occur zero or more times. If the Suppress when empty checkbox is selected (see Figure 5-23), then the element can only occur one or more times; in other words, the element will not appear if it has no content.

Figure 5-23 Target Schema Properties Dialog with Suppress When Empty Option Selected

 

Listing 5-5 shows an XQuery that is generated with the firstname element set to Optional and Suppress when empty. Not that the for loop associated with firstname will affect query efficiency.

Listing 5-5 XQuery returning all BroadBand customers and their Wireless order items, if any. The first_name element is optional and suppressed when empty (code emphasis added)

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	return
	<customer>
		{
		for $firstname_3 in $PB_BB.CUSTOMER_1/FIRSTNAME/text()
		return
		<firstname>{ xf:data($PB_BB.CUSTOMER_1/FIRSTNAME) }</first_name>
		}
		<lastname>{ xf:data($PB_BB.CUSTOMER_1/LASTNAME) }</last_name>
		<orders>
			<order>
				{
				for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_4 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
				where xf:not(xf:empty(
					for $PB_WL.CUSTOMER_ORDER_5 in document("PB-WL")/db/CUSTOMER_ORDER
					where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_5/CUSTOMER_ID)
					  and ($PB_WL.CUSTOMER_ORDER_5/ORDER_ID  eq  $PB_WL.CUSTOMER_ORDER_LINE_ITEM_4/ORDER_ID)
					return
					xf:true()))
				return
				<line_item product={$PB_WL.CUSTOMER_ORDER_LINE_ITEM_4/PRODUCT_NAME} expected_ship_date={$PB_WL.CUSTOMER_ORDER_LINE_ITEM_4/EXPECTED_SHIP_DATE} />
				}
			</order>
		</orders>
	</customer>
	}
</customers>

Optional Attribute and Data Views

In additional to performance considerations — described in Building Queries — you should use the Optional attribute if you plan to use the target schema as part of a data view. The Optional attribute will prevent an unmapped element from appearing as a data source element in the data view.

Caution: If you attempt to use a data view unmapped element as a source element in a new query, the query will fail with a "not mapped" error.

To understand how these attributes affect the query results, experiment with different property settings, run the queries, and compare the results.

Examples Illustrating How Repeatable and Optional Properties Can be Used to Better Filter Query Results

The following two examples show how the combination of elements and joins can be used to filter out data that does not match the query requirements.

Example Set-up

To set up Data View Builder for the following examples, follow these steps:

Create a new project.
Move the following relational database schemas into the work area:
• PB-BB (BroadBand orders RDBMS)
• PB-WL (Wireless orders RDBMS)
Set your target schema to customerLineItems.xsd

Example 1: Retrieve All BroadBand Customers, Returning Their Wireless Orders, If Any (ORDER is Repeatable and Optional)

In this case, the target schema is CUSTOMERS(CUSTOMER*(ORDER*)). The target schema allows for customers with zero orders. This means that the query returns customers even if they have no orders. Practically, this makes the query a left outer-join between customers and orders.

By following these steps you can create this query:

Map the following elements from the BroadBand source to the target schema:

Source: [PB-BB]/db/	Target: [customerLineItems.xsd]/customers/
CUSTOMER/FIRST_NAME	CUSTOMER/FIRST_NAME
CUSTOMER/LAST_NAME	CUSTOMER/LAST_NAME

Map the following elements from the Wireless source to the target schema:

Source: [PB-WL]/db/	Target: [customerLineItems.xsd]/customers/
CUSTOMER_ORDER/ORDER_DATE	orders/order/date
CUSTOMER_ORDER/ORDER_ID	orders/order/id

Create an equal joins [eq] between the following pair of elements by dragging one element over the other:

Join Element	Join Element
[PB-BB]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER_ORDER/CUSTOMER_ID

Enter Test mode. You should see a query similar to that shown in Listing 5-6.

Listing 5-6 Xquery returning all BroadBand customers and returns Wireless orders, if any

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	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>
			{
			for $PB_WL.CUSTOMER_ORDER_6 in document("PB-WL")/db/CUSTOMER_ORDER
			where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_6/CUSTOMER_ID)
			return
			<order id={$PB_WL.CUSTOMER_ORDER_6/ORDER_ID} date={$PB_WL.CUSTOMER_ORDER_6/ORDER_DATE} />
			}
		</orders>
	</customer>
	}
</customers>

Results

Notice that the third customer, John Parker, has no orders (Figure 5-24).

Figure 5-24 Example 1: Query Results (First Four Complex Elements Shown)

 

Example 2: Retrieve Only BroadBand Customers Who Have At Least One Wireless Order; Return Their Wireless orders (ORDER Is Repeatable And Required)

In this example the goal is to be able to check for existence of at least one element before you generate the parent. Generation of required repeatable elements is promoted to the nearest optional repeatable ancestor (or the root of the result, if there is no such element). There the list of elements is computed inside a let clause. After that, the result (list) of the let clause is checked to see if it is empty or not before producing the rest of the result.

The ORDER element is required so you need to check for the existence of orders before producing a customer. This means that you need to generate the list of orders for each customer, and output the customer only if this list is not empty.

The only change needed to the target schema used in Example 2-A is to change the order element from:

repeatable + optional

to

repeatable + required

To do so right-click on the order element (below orders). When the Properties dialog box appears, de-select the Optional checkbox.

Figure 5-25 Orders Element Set to Repeatable and Required

 

 

Now your target schema no longer allows for customers with zero orders. This means that the query will not return customers without orders. This makes the query a natural join between customers and orders.

When you enter Test mode a query similar to that shown in Listing 5-7 will appear.

Listing 5-7 XQuery returning only BroadBand customers with at least one Wireless order (emphasis added)

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	let $order_6 := 
	               for $PB_WL.CUSTOMER_ORDER_7 in document("PB-WL")/db/CUSTOMER_ORDER
	               where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_7/CUSTOMER_ID)
	               return
	               <order id={$PB_WL.CUSTOMER_ORDER_7/ORDER_ID} date={$PB_WL.CUSTOMER_ORDER_7/ORDER_DATE} />
	where xf:not(xf:empty($order_6))
	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_6 }
		</orders>
	</customer>
	}
</customers>

Results

The first four elements returned by the query are shown in Figure 5-26. Since the query filtered out customers without Wireless orders, John Parker no longer appears in the list of customers.

Figure 5-26 Example 2: Query Results (First Three Result Sets Only)

 

 

---

Setting Query Conditions

In the Data View Builder you can define query conditions through XQuery functions in conjunction with constants, query parameters, and custom functions.

You can create conditions (or filters) on source data in two ways:

• Drag-and-drop a source element/attribute onto another source element/attribute to build a conditional statement that defines the default [eq] (equality) functional relationship between the mapped elements/attributes. (See Supported Drag-and-Drop Actions in the Data View Builder.)
• Drag-and-drop source elements/attributes and functions directly into a row on the Conditions tab to build a conditional statement with any of the XQuery functions available from Design tab —> Toolbox tab —> Functions panel.

Working With the Conditions Panel

Conditions are displayed in a panel accessed by the Conditions tab (Figure 5-27). It is in the Conditions area that you can view and change query scoping rules (see Understanding Condition Scoping.)

Enabling or Disabling Conditions

To enable or disable a condition, click the Enabled box to the left of the Condition (see Figure 5-27.)

Figure 5-27 Enabling or Disabling a Condition

 

Removing Conditions

Conditions are displayed in the Design view on the Conditions tab. You can remove a condition by selecting the row that contains it and then clicking the Trashcan button or Delete key. (See Figure 5-28.)

Figure 5-28 Removing a Condition

 

Editing Conditions

To add or delete a condition parameter select the row that contains the condition you want to edit and click the Edit button to bring up the Functions Editor.

 

In the Functions Editor, you can select the parameter you want to delete and click the Trashcan, Delete key, or use the Cut, Copy, Paste options on the Edit menu to modify the condition statement.

For additional information see Using the Function Editor.

Understanding Condition Scoping

When you add a condition to a query, the Data View Builder makes a "best guess" as to the parts of the target schema to which the condition applies. This is known as automatic condition scoping or autoscope, and is determined by:

• Structure of the target schema
• Mappings from source schemas to the target schema
• The conditions themselves

Autoscope should be sufficient for most cases. However, there may be situations in which you want to control condition scoping explicitly. In such cases, you should switch to manual scoping by clicking the checkbox next to Advanced view in the Conditions panel (Figure 5-29).

In Advanced view you can explicitly control the extent that a particular condition applies to the result. For example, you can set scope manually in order to specify which part of a data view is the focal point for a particular condition in the query.

Figure 5-29 Conditions Tab in Basic View

The following sub-topics are discussed in this section:

• Where Scope Applies
• Setting Condition Scope
• Scoping Example

Where Scope Applies

There are three areas where conditions can be scoped:

• Repeatable elements in the target schema
• Repeatable input elements in functions
• Root of the target schema

Note: A repeatable element is identified with either an asterisk [*] or plus [+] sign. (See Managing Target Schema Properties on page 5-26.)

Setting Condition Scope

A common case involving scoping issues occurs when a condition logically applies in two places, but you only want it to appear in one place. You may first notice this when examining the XQuery where clauses or when running the query.

A less common case occurs when you want to create an assertion. For example, you may want to devise your query so that the Liquid Data Server returns a result only when a certain condition occurs. You can accomplish this if you switch to the Advanced view, create the condition, and set the scope for the condition to be the root of the target schema.

Advanced View (Setting Condition Scope Manually)

When Advanced view is selected, the Conditions tab expands to show scoping information. The initial display corresponds to the autoscope setting provided by the Data View Builder.

As an example of Advanced view scope setting in Figure 5-30 the first line (line 0) is selected. The current scoping for that line appears near the top of the Conditions pane: ([customerOrderReport.xsd]/CustomerOrderReport).

Figure 5-30 Conditions Tab in Advanced View Showing Explicit Scope

 

Note: When you switch to Advanced view, it is unnecessary to change any of the explicit scope settings. However, if you add new conditions when in Advanced view, or change existing conditions, you need to manually set the scope for each query condition.

Here are some things to keep in mind when manually setting scope using Advanced view:

• When switching to Advanced view the Current Scope settings initially show the target schema root. Before you map schema sections and create conditions, you can drag a repeatable target schema or function input element to the Current Scope for a complete section of the target schema. Thereafter, the value in the Current Scope text box determines what will appear automatically in a Scope column cell for any new condition that you create.

You can also drag the appropriate repeatable element in the target schema to the Scope column of a particular row. This permits you to refine your query by narrowing where the condition applies.

• The Enabled column contains a switch to include or exclude a condition when the query runs. This operates the same whether in autoscope or manual mode.
• The Condition column shows the source element, condition, and condition target element. This information is the same whether in autoscope or manual mode.
• The Scope column shows which target schema element Liquid Data will use to focus the result. You can also drag a repeatable target schema element directly to a cell in the Scope column to change the scope for that condition.
• The Reset button in the upper right area of the Conditions panel (Figure 5-31) recalculates all scope settings and returns them to the autoscope settings selected by Liquid Data.

When you explicitly define scope you are forcing the XQuery where clause to a specific place in the query or, perhaps, forcing it to be there at all.

 

Note: Condition and Target pairs appear row by row. If there are multiple scope settings for a condition, the condition reappears in separate rows showing each unique scope setting.

Figure 5-31 Advanced View

 

The Current Scope field shows the default scope setting for every condition that you add. If you add a new condition in Advanced view, the default scope is the target schema root until you change that value.

Returning to Autoscope

Caution: When you toggle Advanced view off, Data View Builder returns to autoscope mode. Any changes you made in Advanced view mode are lost and the Current Scope field and Targets column disappear. You will see an alert to this effect when deselecting Advanced view.

Scope Recursion Errors

It is possible to create a query where a condition depends on the values returned by a function, but the function input depends on the condition. For example:

Select the xf:count function and map a source element to be the input of xf:count.
Create a condition that uses the output of the xf:count function.
In Advanced view, set the condition target to the input of the xf:count function.

The xf:count function input must be filtered by applying the condition, but the condition input is the output of xf:count.

Data View Builder does not allow this to happen when in autoscope mode. However, if you set scope manually, it is possible such a circular dependency can happen. Data View Builder cancels the action and generates the error message:

Setting Scope/Target of condition {condition} to {scope element} creates a circular dependency

Recommended Action

If the recursion error message appears, consider resetting all condition scope targets using the Reset button (see Figure 5-31). Or override the automatic settings one at a time, switch to Test view to examine the query, run it, and assess the results.

Scoping Example

This section contains an example illustrating uses of manual scoping.

Resolving Extraneous Joins Through Advanced View Manual Scoping

Advanced View can be used to resolve ambiguous joins.

If you want to create a query that divides products into two groups based on their list price you would create two conditions:

list_price greater-than-or-equal-to $100

list_price less-than $100

Obviously, if both these conditions are applied to the same set of data no data will be returned.

One way to resolve this is to create a second version of the data source schema (see Using Source Schemas Multiple Times in Constructing Queries for an example of this approach). However Advanced Scoping can be used to the same effect, as the following steps show:

Open the Data View Builder and drag the relational data source PB-BB onto the desktop. Expand the data source.
Right-click on the new element in the target schema area; select Add Child.
Enter expensive_products as the local name; click Ok.
Repeat Steps 2 and 3, using cheap_products as the local name.
Back in the data source right-click on the Products complex element; choose Copy.
In the target schema area, right-click on expensive_products; choose Paste and Map. The PRODUCTS complex element will be projected to the target schema under expensive_products.
Right-click on cheap_products; choose Paste and Map. The cheap_products complex element is similarly associated with data.

Figure 5-32 List Price Comparison Project




 

Once you create the project, you need to set your query conditions:

Click on the Conditions tab below the Data View Builder work area.
Click on XQuery Functions. Under Comparison Operators locate the greater-than-or-equal-to [ge] function and drag it to the first Conditions line.
When the Functions editor appears, map the LIST_PRICE element and 100 constant as shown below. Then click Close.

Source	Target: Comparison Function
[PB-BB]/db/PRODUCTS/LIST_PRICE	[ge]/anyValue1
[CONSTANT]/100	[ge]/anyValue2

From the Comparison Operator function list locate the less-than [lt] function and drag it to the first empty Conditions line.
In the Functions editor map the LIST_PRICE element and 100 constant appropriately. Then click Close.

Source	Target: Comparison Function
[PB-BB]/db/PRODUCTS/LIST_PRICE	[lt]/anyValue1
[CONSTANT]/100	[lt]/anyValue2

If you just Test the query (Listing 5-8) at this point no results will appear, as expected, since nothing can fulfill both where clause conditions.

Listing 5-8 List Price XQuery illustrating self-cancelling conditions (emphasis added)

<results>
	<expensive_products>
		{
		for $PB_BB.PRODUCTS_15 in document("PB-BB")/db/PRODUCTS
		where ($PB_BB.PRODUCTS_15/LIST_PRICE  ge  100)
		  and ($PB_BB.PRODUCTS_15/LIST_PRICE  lt  100)
		return
		$PB_BB.PRODUCTS_15
		}
	</expensive_products>
	<cheap_products>
		{
		for $PB_BB.PRODUCTS_21 in document("PB-BB")/db/PRODUCTS
		where ($PB_BB.PRODUCTS_21/LIST_PRICE  ge  100)
		  and ($PB_BB.PRODUCTS_21/LIST_PRICE  lt  100)
		return
		$PB_BB.PRODUCTS_21
		}
	</cheap_products>
</results>

To resolve this problem click Advanced view in the Conditions section. You will notice that instead of the two conditions you created, four are listed. This is because Advanced view shows you the actual where clause conditions used in the query, based on application of the Data View Builder best-guess autoscope rules.

Figure 5-33 Advanced View of Conditions in List Price Project




 

If you disable the inappropriate conditions (Figure 5-34), Advanced View will appear as we expected it should, with a single WHERE condition for each section of the query.

Figure 5-34 Advanced View With Two Conditions Disabled

 

The newly generated XQuery is correct (Listing 5-9).

Listing 5-9 List Price XQuery after extraneous conditions are disabled

<results>
	<expensive_products>
		{
		for $PB_BB.PRODUCTS_15 in document("PB-BB")/db/PRODUCTS
		where ($PB_BB.PRODUCTS_15/LIST_PRICE  ge  100)
		return
		$PB_BB.PRODUCTS_15
		}
	</expensive_products>
	<cheap_products>
		{
		for $PB_BB.PRODUCTS_21 in document("PB-BB")/db/PRODUCTS
		where ($PB_BB.PRODUCTS_21/LIST_PRICE  lt   100)
		return
		$PB_BB.PRODUCTS_21
		}
	</cheap_products>
</results>

And the results (Figure 5-35) conform with the query.

Figure 5-35 List Price XQuery Results Show Three "Expensive" Products and Two "Cheap" Products

 

Task Flow Model for Advanced View Manual Scoping

If you decide to override automatic scope settings, there is a workflow model that can help you design the query, create conditions, and determine the scope. By following this methodology, you will find it is easy to create a query where you control the scope. Consider the project shown in Figure 5-36 which has two source schemas: PB-BB and PB-WL, and the target schema customerLineItems.xsd.

Figure 5-36 Schemas for Manual Scope Example

 

The target schema, customerLineItems.xsd, has a hierarchical structure. There are three distinct sections in the schema that represent repeatable data. Elements customer and order each have an asterisk [*] as the occurrence indicator. The element line_item has a plus sign [+] as its occurrence indicator. This means that the child nodes without an asterisk or plus are non-repeating.

For each customer, there is one occurrence of first_name, last_name, and id. Each customer may have zero or more orders. When an order exists, each order has one id, date, and amount. If an order exists, there must be at least one line_item. Work on sections that appear under a repeatable node.

This workflow model assumes that you can build your query in steps, focusing on each section in the target schema as you go. Follow these steps for each section in the target schema where you want a result to appear:

Choose a repeatable section of the target schema for the scope. A section is a repeatable node (parent) and its children. It is recommended that you work from the outside in. In this case, the outermost section is the customer* section. (For this example you want to collect first_name, last_name, and id in the result.)
Set the highest repeatable node in this section as the default scope, which in this case is customer*. Drag that element from the target schema onto the Current Scope text box on the Conditions tab. (For this example we drag and drop customerLineItems.xsd onto the Current Scope text box.)




 
Map selected source elements/attributes to that repeatable section in the target schema.

For this example, we do the following mappings:

• Map [PB-WL]/db/CUSTOMER*/FIRST_NAME to [customerLineItems.xsd]/customers/customer*/first_name
• Map [PB-WL]/db/CUSTOMER*/LAST_NAME to [customerLineItems.xsd]/customers/customer*/last_name
• Map [PB-WL]/db/CUSTOMER*/CUSTOMER_ID to [customerLineItems.xsd]/customers/customer*/id
Set any conditions that connect and filter the mapped sources.

By setting the default scope before creating the condition, Data View Builder sets the condition scope to that value.

By mapping one section at a time and using the repetitive ancestor node as the default scope, your conditions will apply exactly where you need them to appear in the result.

For this example, you set as a condition a join between CUSTOMER_ID in the PB-BB schema and CUSTOMER_ID in the PB-WL schema (Figure 5-37).

Figure 5-37 Project Showing Join on CUSTOMER_ID




 


 

Repeat these steps for each section of the target schema where you want data to appear in the result. Work on one section at a time and work from the outside (more general) to the inside (most specific). Ensure that you set the default target, map, and define the conditions, before you move to the next section. The general rule is that any mapping with an associated condition requires a scope setting.

In a small number of cases, you may apply a condition on the argument (input) to a function that requires choosing the function as the default scope. This is not common but will occur when you choose a complex aggregate function.

 

---

Sorting Query Results

The Sort By tab allows you to specify how query results should be ordered. The screen shot of the Sort By Tab Dialog Box (Figure 5-38) contains a single data source with a repeatable and optional complex element called PROMOTION_PLAN.

Figure 5-38 Sort By Tab Dialog Box

 

The Sort By tab allows you to define the output order for any repeatable element, as identified by a plus [+] or asterisk [*] next to its name. An element can be sorted by one or more sub-elements (including itself in the case of a simple element). (You can change an attribute setting of a complex element to repeatable. For details see Managing Target Schema Properties.)

Follow these steps to change sorting order of an element:

Select an element from the Sort drop-down list.
To specify a sub-element to sort by, select the sub-element from the By column, then set the direction.
The relevant sorting order can be modified by selecting a line and using the Up or Down arrows.

In the case of the project shown in Figure 5-38, you are sorting elements in PROMOTION_PLAN first by PROMOTION_NAME and then by PLAN_NAME. The PROMOTION_NAME element will be sorted in ascending order while PLAN_NAME will be Descending.

If you set the topmost sort element to PRICE and the direction to Descending, the result of the query will be ordered appropriately. See Figure 5-39.

Figure 5-39 Results Sorting by Price in Descending Order

 

 

---

Using Existential Condition Checking in Queries

An existential condition tests for the existence of an underlying data relationship that fits specific criteria.

The Data View Builder offers an option that potentially introduces additional existential conditions in a XQuery. This condition or conditions can be used to further filter query results such as eliminating duplicates being returned by a query. Because extra processing is involved, adding existential conditions can impact query performance.

To activate the option select Allow Existential Condition Generation from the Query menu. A checkmark next to the option indicates that it is active.

The following pseudocode shows an existential condition test. The where-for routine will return an xf:true() if the enclosed conditions are fulfilled and execution will proceed. If the conditions are not fulfilled, the return data will not be executed.

				...				

						where xf:not(xf:empty(
							for ...
									where ... 
							return
							xf:true()))
				return
					data

An Existential Example

The following example illustrates a case where the existential condition generation option affects query results.

To set up Data View Builder, follow these steps:

Create a new project.
Move the following relational database schemas into the work area:
• PB-BB (BroadBand orders RDBMS)
• PB-WL (Wireless orders RDBMS)
Set your target schema to customerLineItems.xsd
Map the following elements from the BroadBand source to the target schema:

Source: [PB-BB]/db/	Target: [customerLineItems.xsd]/customers/
CUSTOMER/LAST_NAME	CUSTOMER/last_name
CUSTOMER/CUSTOMER_ID	CUSTOMER/id

Map the following elements from the Wireless source to the target schema:

Source: [PB-WL]/db/	Target: [customerLineItems.xsd]/customers
PRODUCTS/PRODUCT_NAME	orders/order/line_item/product
CUSTOMER/CUSTOMER_ID	CUSTOMER/id

To ensure referential integrity create joins between the following pairs of elements by dragging one element over the other:

Join Element	Join Element
[PB-BB]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER/CUSTOMER_ID
[PB-WL]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER_ORDER/CUSTOMER_ID
[PB-BB]/db/CUSTOMER_ORDER/ORDER_ID	[PB-WL]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID
[PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/PRODUCT_NAME	[PB-WL]/db/PRODUCTS/PRODUCT_NAME

Note that no order_id is projected to the target schema. Therefore all products ordered by a particular customer will be returned in a single group. The generated query makes this relationship clear.

Listing 5-10 Example XQuery With Existential Condition Generation Off (Default Condition)

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	return
	<customer id={$PB_BB.CUSTOMER_1/CUSTOMER_ID}>
		<last_name>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</last_name>
		<orders>
			<order>
				{
				for $PB_WL.PRODUCTS_4 in document("PB-WL")/db/PRODUCTS
				for $PB_WL.CUSTOMER_5 in document("PB-WL")/db/CUSTOMER
				for $PB_WL.CUSTOMER_ORDER_6 in document("PB-WL")/db/CUSTOMER_ORDER
				for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_7 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
				where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_5/CUSTOMER_ID)
				  and ($PB_WL.CUSTOMER_5/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_6/CUSTOMER_ID)
				  and ($PB_WL.CUSTOMER_ORDER_6/ORDER_ID  eq  $PB_WL.CUSTOMER_ORDER_LINE_ITEM_7/ORDER_ID)
				  and ($PB_WL.CUSTOMER_ORDER_LINE_ITEM_7/PRODUCT_NAME  eq  $PB_WL.PRODUCTS_4/PRODUCT_NAME)
				return
				<line_item product={$PB_WL.PRODUCTS_4/PRODUCT_NAME} />
				}
			</order>
		</orders>
	</customer>
	}
</customers>

The query in Listing 5-10 returns a list of every item ordered by the particular customer, including duplicates, if any. (See Listing 5-11.)

Listing 5-11 Query Results for CUSTOMER_1 With Existential Condition Checking Inactive

<customers>
  <customer id="CUSTOMER_1">
    <last_name>KAY_1</last_name>
    <orders>
      <order>
        <line_item product="E110"/>
        <line_item product="E110"/>
        <line_item product="E900"/>
        <line_item product="E900"/>
        <line_item product="NOK9250"/>
        <line_item product="NOK9250"/>
        <line_item product="S625"/>
        <line_item product="S625"/>
        <line_item product="SS8"/>
        <line_item product="SS8"/>
      </order>
    </orders>
  </customer>
...
</customers

When the Allow Existential Condition Generation option is active, a where xf:not(xf:empty) condition is applied that effectively filters out the return of duplicate order items. The resulting query is shown in Figure 5-12.

Listing 5-12 Example XQuery With Existential Condition Generation On (emphasis added)

<customers>
	{
	for $PB_BB.CUSTOMER_1 in document("PB-BB")/db/CUSTOMER
	return
	<customer id={$PB_BB.CUSTOMER_1/CUSTOMER_ID}>
		<last_name>{ xf:data($PB_BB.CUSTOMER_1/LAST_NAME) }</last_name>
		<orders>
			<order>
				{
				for $PB_WL.PRODUCTS_4 in document("PB-WL")/db/PRODUCTS
				where xf:not(xf:empty(
					for $PB_WL.CUSTOMER_5 in document("PB-WL")/db/CUSTOMER
					for $PB_WL.CUSTOMER_ORDER_6 in document("PB-WL")/db/CUSTOMER_ORDER
					for $PB_WL.CUSTOMER_ORDER_LINE_ITEM_7 in document("PB-WL")/db/CUSTOMER_ORDER_LINE_ITEM
					where ($PB_BB.CUSTOMER_1/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_5/CUSTOMER_ID)
					  and ($PB_WL.CUSTOMER_5/CUSTOMER_ID  eq  $PB_WL.CUSTOMER_ORDER_6/CUSTOMER_ID)
					  and ($PB_WL.CUSTOMER_ORDER_6/ORDER_ID  eq  $PB_WL.CUSTOMER_ORDER_LINE_ITEM_7/ORDER_ID)
					  and ($PB_WL.CUSTOMER_ORDER_LINE_ITEM_7/PRODUCT_NAME  eq  $PB_WL.PRODUCTS_4/PRODUCT_NAME)
					return
					xf:true()))
				return
				<line_item product={$PB_WL.PRODUCTS_4/PRODUCT_NAME} />
				}
			</order>
		</orders>
	</customer>
	}
</customers>

As noted above, both results are valid. For performance reasons it is recommended that where appropriate queries be run without the additional existential condition generation. In many cases duplicate results reporting may be sought or acceptable. In other cases the underlying data may make such existential condition checks unnecessary.

Listing 5-13 Query Results for CUSTOMER_1 With Existential Condition Checking Active

<customers>
  <customer id="CUSTOMER_1">
    <last_name>KAY_1</last_name>
    <orders>
      <order>
        <line_item product="E110"/>
        <line_item product="E900"/>
        <line_item product="NOK9250"/>
        <line_item product="S625"/>
        <line_item product="SS8"/>
      </order>
    </orders>
  </customer>
..
</customers>

Note: Opening a project saved with Liquid Data 8.1 SP1 or earlier will make the Allow Existential Condition Generation active in order to preserve backward compatibility.

 

---

Using Automatic Type Casting

Automatic type casting helps ensure that input parameters used in functions and mappings are appropriate to the function in which they are used.

Select Automatic Type Casting on the Query menu to ensure that Liquid Data will assign (cast) a new data type when:

The source element data type does not match the mapped target element data type and
The source element is eligible to be type cast to the target element data type.

An checkmark next to the Automatic Type Casting option on the Query menu indicates that it is on.

When function parameters have a numeric type mismatch, the Liquid Data Server can promote the input source to the input type required by the function if the promotion adheres to the prescribed promotion hierarchy. The promotion hierarchy exists only for numeric values.

Table 5-40 Numeric Data Type Promotions

 

If the type mismatch requires casting in reverse order, the Liquid Data Server does not attempt type casting. In this case, the Data View Builder attempts to type cast but the results may be unpredictable.

An example: If the required function input type is xs:decimal, then source data that is integer, long, int, short, or byte can easily be promoted to a data type with more precision or larger number of digits. The server will complete that task. However, if the input function type is xs:double or xs:float and the required input type is xs:integer or xs:byte, the Data View Builder tries to cast, but there may be unpredictable rounding or truncating of the result. All other type mismatches, such as xs:date, xs:dateTime, or xs:string, require a type cast to avoid a type mismatch error.

Clear the Automatic Type Casting check box to disable this feature.

Automatic Type Casting Transformations

This section provides specifics on how the Data View Builder implements data type transformation for automatic type casting. The following topics are included:

• Automatic Type Casting to a Numeric Target
• Automatic Type Casting to a Non-Numeric Target
• Automatic Type Casting Function Parameters

You can use the information in the following sections to predict automatic type casting behavior.

Automatic Type Casting to a Numeric Target

The following table shows whether Liquid Data transforms a source element data type to the numeric data type of the target element.

 

Automatic Type Casting to a Non-Numeric Target

The following table shows whether Liquid Data transforms a source element data type to the non-numeric data type of the target element.

 

Note: The type cast from xs:dateTime to xs:date and xs:time uses xfext:date-from-dateTime() and xfext:time-from-dateTime.

Automatic Type Casting Function Parameters

In some cases, Liquid Data can transform the data type for a function parameter when a mismatch occurs.

 

Exceptions to Automatic Type Casting

Liquid Data does not type cast comparison operators (such as eq, le, ge, ne, gt, lt, or ne) or any functions that accept xsext:anytype.

Type casting does not apply to function parameters or to target schema elements/attributes that require the following data types:

• xsext:item
• xsext:anyValue
• xsext:anyType
• Any other data type that cannot be cast

If the source data is not compatible with the data type of the target element, automatic type casting will not improve query results. For example, mapping a date to a numeric type may not produce useful results.

Note: You may not see an error on a type mismatch until the Liquid Data Server tries to run the query.

Contact BEA |  Feedback |  Privacy  |