Building Queries and Data Views
|
 |
 |
|
|
 |
 |
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:
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:
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.
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.
PB-WL data source to open the associated XML schema showing Wireless customers.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.
amtByState.xsd
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>
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
You can use the Data View Builder Function Editor to build up XQuery functions (see Using XQuery Functions for more information).
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.
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:
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:
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.
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.
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:
<CUSTOMER_ID>{ () }</CUSTOMER_ID>
<STATE/>
To include a constant as a function parameter, follow steps similar to those in the following example:
starts-with function. You get the following placeholder in the Functions Editor:xf:starts-with(str1,str2)
str1). For example, choose CustomerID from a source schema.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.
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:
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
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.)
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.
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.
The Data View Builder supports the drag-and-drop actions that are described in the following table.
Table 5-14 Supported Mapping Relationships
|
Map simple element from one source to another simple element in another source |
Creates an equality [ |
|
A data element is used as an input parameter to a function. (You can also provide constants and variables as function parameters.) |
|
|
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. |
|
|
A function (f1) output can be input to an element in a source 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. |
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.
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 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:
There are several benefits of mapping or projecting elements:
Figure 5-16 shows the results of the mapping of a complex element to the target schema. The mapping was accomplished by:
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>
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:
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.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>
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.Mapped elements/attributes in a query are displayed on the Mappings tab. You can delete mappings between elements and attributes by:
Figure 5-18 Removing a Mapping
You can make changes to a target schema by right-clicking an element. A pop-up menu displays available options.
|
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. |
|
|
Copies the selected schema element or attribute to the clipboard. |
|
|
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. |
|
|
Appends a complex element as a child to the selected element. Properties of the copied source complex elements and its children cannot be changed. |
|
|
Converts an element mapped to a set of individual value mappings. |
|
|
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 |
|
|
Appends a new element as a child to the selected element. By default the name of the new attribute is |
|
|
Creates a new element as a parent of the selected element or attribute. This also increases the nesting level of the selected element. |
|
|
Removes a selected element/attribute. If the element/attribute to be deleted is mapped, Data View Builder will first display a warning. |
|
|
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. |
|
|
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. |
|
|
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. |
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
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.
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>
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.
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.
To set up Data View Builder for the following examples, follow these steps:
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:
eq] between the following pair of elements by dragging one element over the other:
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>
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)
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:
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>
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)
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:
eq] (equality) functional relationship between the mapped elements/attributes. (See Supported Drag-and-Drop Actions in the Data View Builder.)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.)
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
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
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.
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:
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:
There are three areas where conditions can be scoped:
Note: A repeatable element is identified with either an asterisk [*] or plus [+] sign. (See Managing Target Schema Properties on page 5-26.)
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.
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:
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.
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.
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.
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.
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:
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:
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.
This section contains an example illustrating uses of 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:
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:
expensive_products; choose Paste and Map. The PRODUCTS complex element will be projected to the target schema under expensive_products.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:
ge] function and drag it to the first Conditions line.LIST_PRICE element and 100 constant as shown below. Then click Close.
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>
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
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:
customer* section. (For this example you want to collect first_name, last_name, and id in the result.)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.)
[PB-WL]/db/CUSTOMER*/FIRST_NAME to [customerLineItems.xsd]/customers/customer*/first_name[PB-WL]/db/CUSTOMER*/LAST_NAME to [customerLineItems.xsd]/customers/customer*/last_name[PB-WL]/db/CUSTOMER*/CUSTOMER_ID to [customerLineItems.xsd]/customers/customer*/idBy 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
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.
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:
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
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
The following example illustrates a case where the existential condition generation option affects query results.
To set up Data View Builder, follow these steps:
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.
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:
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.
This section provides specifics on how the Data View Builder implements data type transformation for automatic type casting. The following topics are included:
You can use the information in the following sections to predict automatic type casting behavior.
The following table shows whether Liquid Data transforms a source element data type to the numeric data type of the target element.
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.
In some cases, Liquid Data can transform the data type for a function parameter when a mismatch occurs.
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:
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.
|
|
|
