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

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:

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.

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:

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:

Boolean expressions test true or false. You can specify that the Boolean query parameter has an implicit definition of True or False, then use it as query resource.

xs:byte (byte)

A positive or negative whole number. The maximum value is 127 and the minimum value is -128. For example:

-1

0

126

+100

xs:date (date)

Input must be in this format: MMM dd, YYYY

For example:

JUN 12, 2002

xs:dateTime (datatype)

Input must be in this format:

MMM dd, YYYY HH:MM:SS AM/PM

For example:

MAY 12, 2002 12:12:11 AM

xs:decimal (decimal)

A precise real number (negative or positive) that can contain a fractional part. If the fractional part is zero, the period and following zero(s) can be omitted. For example:

-1.23

12678967.543233

+100000.00

210.

xs:double (double)

A real number (negative or positive) that can contain fractional part. For example: 3.159

Liquid Data does not support floating point formats expressed in fractions (½) or IEEE floating point notation (3E-5).

xs:float(floating point)

A real number (negative or positive) that can contain a fractional part. For example:

100.0

-100.5

Note: Liquid Data does not support floating point formats expressed in fractions (½) or IEEE floating point notation (3E-5).

xs:int (int)

A positive or negative whole number. The maximum value is 2147483647 and minimum value is -2147483648. For example:

-1

0

126789675

+100000

xs:integer (integer)

A positive or negative whole number. The maximum value is 2147483647 and minimum value is -2147483648. For example:

1

-100

+100

xs:long (long)

A positive or negative whole number. The maximum value is 9223372036854775807 and minimum value is-9223372036854775808. For example:

-1

0

12678967543233

+100000

xs:short (short)

A positive or negative whole number. The maximum value is 32767 and minimum is -32768. For example:

-1

0

126789

+10000

String (xs:string)

An alphanumeric expression such as:

Smith

Jones

12345 State St.

Note: An unspecified value for a query parameter of type String is considered an empty string.

Time (xs:time)

Input must be in this format: HH:MM:SS AM/PM

For example:

01:02:15 AM

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.

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.

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

Action

Description

Map simple element from one source to another simple element in another source

Creates an equality [eq] relationship between the two elements/attributes using the [eq] (equals) function. These can be in the same or different source schemas.

Map simple element to a function

A data element is used as an input parameter to a function. (You can also provide constants and variables as function parameters.)

Each function has its own specification of parameters. The output from a function can be input to another function. See Example 2: Aggregates in Liquid Data by Example (specifically, the Add the count function within the Aggregates example).

Map simple element to a target element

Projects data element onto the target schema. Most query examples provided in this documentation show how to map source schema elements/attributes to target elements/attributes.

Map function output to target schema

A function (f1) output can be input to an element in a source schema.

Copy, then Paste a complex element to a target schema

Copies the structure of the complex element, including its simple elements and attributes, to the target schema. In order for these elements to be included in the generated query they must first be individually mapped.

Copy, then Paste and Map a complex element to a target schema

Copies the complex element to the target schema. Content of the element are shown in italics for information only. A generated query will treat the complex element as a unit. See Complex Element Mappings.

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.

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.

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.

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.

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:

Modifying Target Schemas

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

Option

Effect

Expand

Expands to show any hidden child elements.

Properties

Allows you to set or inspect element properties. Depending on the element selected, properties that may be changed include local name, namespace, content type, repeatable, and optional.

Copy

Copies the selected schema element or attribute to the clipboard.

Paste

Appends the copied element and any children to the selected element. If a copied element contains cloned elements/attributes, the Data View Builder pastes them as regular elements/attributes. Only the hierarchical structure transfers.

Notes:

If a pasted element is a duplicate, Data View Builder renames the element as NAME(2), NAME(3) and so on.

The Paste function works only with elements. You cannot paste an element to an attribute.

This menu item is only available when you have data on the clipboard.

Paste and Map

Appends a complex element as a child to the selected element. Properties of the copied source complex elements and its children cannot be changed.

Notes:

Sub-elements are shown as mapped and in italics. Any generated query treats complex elements as a unit.

You cannot Paste and Map a complex element of the same name to the same level of the target schema. If you try to do so you will get a "Clones of mapped element types are not allowed" error. Options include deleting or renaming the original complex element in the target schema.

Expand Complex Mappings

Converts an element mapped to a set of individual value mappings.

Notes:

After expanding complex mappings you can delete individual mappings using the Trashcan or Delete key.

The only way to Undo this operation is to delete the mappings and Paste and Map again.

Add Attribute

Allows you to add an attribute to the selected element. Attribute properties include local name, namespace, content type, and optional. By default the name of the new attribute is new_attribute.

Add Attribute works only on an element.

Add Child

Appends a new element as a child to the selected element. By default the name of the new attribute is new_attribute.

Add Child works only on an element.

Add Parent

Creates a new element as a parent of the selected element or attribute. This also increases the nesting level of the selected element.

The name of the new element is, by default, new_element.

Delete

Removes a selected element/attribute. If the element/attribute to be deleted is mapped, Data View Builder will first display a warning.

Move up

Moves the element/attribute (and children, if any) higher in the list of siblings in the schema tree. An element or attribute can only move up or down among siblings.

Move down

Moves the element/attribute (and children, if any) lower in the list of sibling on the schema tree. An element or attribute can only move up or down among siblings.

Clone

Duplicates the selected element to the same level of the schema hierarchy. Unlike a Copy/Paste operation, cloning does not change your physical schema. You would use cloning if you were, for example, adding a second data source for the same type of information (such as customer orders).

The Union example in Liquid Data by Example illustrates a use of the clone command.

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:

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:

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.

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

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 (beloworders). 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)

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

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.

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.

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:

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.

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.

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.

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.

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.

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.

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

Type

Promoted Type

byte

short

short

int

int

long

long

integer

integer

decimal

decimal

float

float

double

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

Exceptions to Automatic Type Casting

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.