bea.com | products | dev2dev | support | askBEA

Download Docs | Site Map | Glossary
Search

Building Queries and Data Views

Building a Query

Building a query involves specifying one or more source schemas that describe resource data, selecting a single target schema that describes the shape of the query result, creating source-to-target mappings to further define what the query result will look like, and defining source conditions or filters on the data sources. The query extracts the results based on the conditions and mappings that you define in the query. The results can change dramatically depending on how you do the following:

- Specify conditions (filters on the source data)

- Map or project source data from one or more sources to the target schema.

If you have taken the time to outline a design for the query first, considering all the factors mentioned in the previous section (Designing Queries), constructing it will be a matter of following your design as a blueprint for drag-and-drop query building. Then you can test, fine-tune, and modify as needed to produce variations on the results, or to optimize the query for better performance.

The following sections take you through the basic tasks involved in building a query:

- Opening the Source Schemas for the Data Sources You Want to Query

- Adding a Target Schema

- Mapping Source and Target Schemas

- Setting Conditions

Opening the Source Schemas for the Data Sources You Want to Query

A source schema is the XML schema representation for the structure of the data in a data source. You can use multiple data source schemas per query.

The Sources tab on the Builder Toolbar contains the data sources configured on the Liquid Data Server to which you are connected. Note that a data source type only shows up as a button on the Builder Toolbar if it has been configured in the Server to which you are connecting.

Note: Only data sources that have been configured for access by Liquid Data are available from the Builder Toolbar. For information on how to configure Liquid Data data sources, see the Liquid Data Administration Guide.

For this example, open the schemas for the following two data sources which are already configured on the Liquid Data Samples server:

- PB-WL relational database

- XM-BB-C XML file

To do this, follow these steps:

1. Click the Design tab.

2. On the Builder Toolbar, click the Sources tab (on the bottom of the left vertical panel).

3. Open the data sources from the Builder toolbar as follows:

   - Click the Relational Databases and double-click on PB-WL data source to open the associated XML schema showing "Wireless" customers.

   - Click the XML Files button in the navigation panel and double-click on XM-BB-C data source to open the associated XML schema showing "BroadBand" customers.

   The XML schemas for the each of the data sources are displayed.

   Position the schema windows so you can view the data nodes in each schema. You can expand the data nodes by clicking the plus (+) sign. For example, in the PB-WL data source, CUSTOMER is a parent node with subordinate child nodes. The child nodes are the ones you will use as function parameters and map to the target schema.

Adding a Target Schema

A target schema is the XML schema representation for the structure of the target data (query result). Only one target schema per project is allowed. If you have a target schema open and decide to choose another, the current target schema is closed and the new one replaces it.

You can use a target schema file that you have saved on your local system or on the network, or one that has been saved to the Liquid Data server repository.

Note: Only target schemas that are saved to the Liquid Data server repository will be available to other Liquid Data users for distributed, team-style development.

For this example, we will use a target schema called amtByState. If this schema is not available in the Samples server repository and you would like to follow along with our example, you can create it yourself and save it locally or to the server repository as a .xsd file.

To create and set the target schema do the following:

1. Use a text editor to copy the following XML into a plain text file and save it to the server Repository as amtByState.xsd.

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

>     <WL_HOME>liquiddata/samples/config/ld_samples/repository/schemas/

Note: It is not necessary to save the target schema to the server Repository in order to use it in your local project—you can save it anywhere on your system. However, we recommend saving schemas to the Repository because it makes projects more "portable" and schema files accessible to all users who log onto this server.

Listing 3-1 XML Source for amtByState.xsd Target Schema File

>     <?xml version = "1.0" encoding = "UTF-8"?>

>     <xsd:schema xmlns:xsd = "http://www.w3.org/2001/XMLSchema">
>      <xsd:element name="customers">
>       <xsd:complexType>
>        <xsd:sequence>
>         <xsd:element 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>

1. In the Data View Builder, choose File—>New Project to clear your desktop and reset all default values.

2. Choose the menu option File—>Set Target Schema.

   Navigate to the server Repository or to the location where you saved the amtByState.xsd schema. Choose amtByState.xsd and click Open.

   amtByState.xsd appears as the target schema.

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

Note: Remember that only one target schema per project is allowed. The target schema docks on the right side of the desktop area. The target schema may have more data nodes than you need for your result, but it must contain the data nodes required for the query result. Unreferenced nodes are disregarded in the result.

Editing a Target Schema

You can make simple changes to a target schema by right-clicking a node. A shortcut menu shows the editing functions that are available.

Function | Rules
--- | ---
Copy | You can copy both a source and a target node if: The node or its parent is not a clone. The node or its parent has not been cloned.
Paste | Appends the copied node and its children as a child of the selected node. If a copied node contains cloned nodes, Data View Builder pastes them as regular nodes. Only the hierarchical structure transfers. If a pasted node is a duplicate, Data View Builder renames the node as _copy1, _copy2, and so on. Pasted nodes lose any mapping attributes; however, Data View Builder will display a warning and allow you to abandon the task. The paste function works only on an element node. You cannot paste a child node to an attribute node. This menu item is unavailable unless you have data on the clipboard.
Add a Child (Node or Attribute) | Appends a new node or attribute as a child to the selected node. The name of the new element or node is new_node or new_attribute. The add function works only on an element node. You cannot add a child node to an attribute node. Note: Only string type data is supported.
Delete | Removes a selected node. If the node to be deleted is a mapped node, Data View Builder will display a warning and allow you to abandon the task.
Rename | Allows you to rename the selected node. An error message appears if the new name is a duplicate of a node at the same hierarchical level. As detailed in the XML specification, target schema element names must consist of ASCII characters and must not include double-byte characters or single-byte Katakana characters.

Mapping Source and Target Schemas

Mapping is a visual relationship structure among the critical data elements in the query. When you combine these relationships with functions, you have a set of instructions for the query generation engine. If you think of the selected data nodes as the nouns (what we want to work on), the functions as the verbs (the action), then the mapping among the data elements creates a logical sentence that expresses the query.

Before you begin creating a query, it is important to expand the nodes in the source schemas to reveal the data elements that you want to use at their lowest level. To expand a node, right click on it and choose Expand.

Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, 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 Node to Node

You can use drag-and-drop mappings from one element or attribute to another to create conditions on source data and source-to-target mappings that will define the shape of the query result.

Mapping Nodes to Create Conditions on Source Data

Choose one of the following methods to map a source schema node to another source schema node to create a Condition.

- Drag and drop a source schema element/attribute to another source schema element/attribute to define an eq (equality) source condition

Or

- For all functions other than eq (equality), drag and drop the function to the first empty row in the Condition column in the Work area first before you drag and drop elements/attributes as function parameters. Then drag a source schema element/attribute and drop it into the same row of the Condition column. Drag a second source schema element/attribute and drop it into the same row of the Condition column.

Mapping Nodes to Create Source-to-Target Mappings

Choose one of the following methods to map a source schema node to a target schema node.

- Drag and drop a source schema element/attribute to a target schema element/attribute.

- On the Mappings tab, drag-and-drop a source schema element/attribute into a row in the Sources column and drag-and-drop a target schema element/attribute into the same row in the Target column. For all functions other than eq (equality), drag-and-drop the function first before you drag and drop elements or attributes as function parameters.

Note: You cannot map the same node from more than one source schema to a single node in the target schema. For example, if you map STATE (under CUSTOMER) from the Broadband database to state? in the target schema, you cannot successfully map STATE from a second source schema to state? in the target schema. The last mapping completed is the only mapping from source to target that the query generation engine processes. If you need to create a relationship among all three STATE elements, map the element in one source schema to the element in the second source schema. Then map one of the source elements to the target element.

Example: Query Customers by State

Figure 3-1 shows 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.

Next, to project a result we designate what the output of this relationship should look when the query runs. By mapping one of the sources to the target, we specify that we want to store the result in the target schema. Because we are collecting only information about states and defining only one element in the target schema, we are in effect asking that Liquid Data fill only that data element in the result when the query runs.

To do this, drag and drop the STATE element in PB-WL source schema onto the state? element (under STATE*) in the Target schema.

See Figure 3-1 for an example of the mappings described in this example.

Figure 3-1 Mapping Element to Element

Mapping Nodes to Functions

When you drag and drop a source node onto another source node (either within the same source schema or among different source schemas) you are automatically creating an equality relationship between the two elements/attributes using the eq (equals) function. In other words, the eq function is mapped by default for all drag-and-drop relationships you create among source elements/attributes.

You can also create the same equality relationship the "long" way by dragging and dropping the eq function onto a row in the Conditions tab and then dragging and dropping source elements/attributes into the same row, or by opening the Functions Editor and dragging and dropping the function and elements directly into the Editor.

To use any of the available functions other than eq (equality) function, you must use this second method of dealing directly with the functions as described below.

To use a function:

1. Drag and drop the function from the Toolbox "Functions" panel to the first empty row under the "Conditions" on the Conditions tab.

2. Drag a source schema node and drop it into the same row of the Condition column. Drag a second source schema node and drop it into the same row of the Condition column.

To edit an existing functional relationship:

1. Open the Functions Editor by clicking the Edit button.

2. Edit the statement as needed. You can delete the current parameters or function, and drag and drop a new function and source elements/attributes into the Functions Editor.

Figure 3-2 shows the functional relationship of equality (eq) between two source elements that was created by default when you mapped the source elements in Example: Query Customers by State. (Note that you could have created this same relationship directly in the Functions Editor the way you would create any other functional relationship between elements/attributes.)

Figure 3-2 Mapping Elements to Functions

To get the view shown in Figure 3-2, click on the Conditions tab, select the row with the condition in it to activate the Edit button, and click the Edit button. This displays the condition in the Functions Editor.

For more information about functions, see What are Functions?.

For more information about using the Functions Editor and working with functions on the UI, see XQuery Functions and The Function Editor in Starting the Builder and Touring the GUI.

Supported Mapping Relationships

Data View Builder and Liquid Data support any of the Mapping actions described in the following table.

Table 3-1 Supported Mapping Relationships

Types of Mappings | Description
--- | ---
Source node to another source node | Creates an equality relationship between the two elements/attributes using the eq (equals) function. The eq function is used by default for all drag-and-drop mappings created among source elements/attributes to create a condition that will filter for matching items found.
Source node to a function | The data becomes 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. For an example of this, see Example 2: Aggregates in Query Cookbook (specifically, the step Ex 2: Step 8. Add the "count" Function within the Aggregates example).
Source node to a target node | By mapping a source to a target, you are projecting, or storing, the data onto the target schema. All query examples provided in this documentation show how to map source schema elements/attributes to target elements/attributes. For example, see Example: Return Customers by Name and Example: Query Customers by ID and Sort by State.
Function to target node | A function (f1)output can be another function's (f2) input. For an example of this, see Example 2: Aggregates in Query Cookbook (specifically, the step Ex 2: Step 8. Add the "count" Function within the Aggregates example).

Removing Mappings

Mapped elements/attributes in a query are displayed on the Mappings tab. You can change your mind and remove a mapping by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-3.)

Figure 3-3 Removing a Mapping

Setting Conditions

You can create conditions or filters on source data by doing any of the following:

- Drag-and-drop a source node onto another source node to build a conditional statement that defines the default eq (equality) functional relationship between the mapped elements/attributes. (See Supported Mapping Relationships.)

- 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 functions available from Design tab—> Toolbox tab—>Functions panel.

What are Functions?

Functions are used as the verbs or actions in condition statements that establish relationships between or operations on data source elements or attributes. (The data source elements/attributes become one type of parameter to the functions.) A function is a built-in executable process that manipulates the data to perform a task. You must pass one or more parameters, which can be source data, variables, or constant values, for the function to produce output. The function returns a result to you based on the conditional statements you build and how you specify where to store the result.

In the previous example (Example: Query Customers by State) we defined the default equality relationship between two source elements (by dragging and dropping the CUSTOMER "STATE" element from one source to another); then defined the result by dragging and dropping the CUSTOMER "STATE" element from one of the source schemas onto the analogous "STATE" element in the target schema.

If you need to find out something other than information based on equality, you will need to use a different function. For example, suppose you want to find out how many customer IDs in the Broadband database are not equal to those in the Wireless database. The default functional action is to look for equality. If you simply map one customer ID source element/attribute to the other, the query engine looks for those instances of matching data, or equality.

(For the relationship of not equal to, you need to go to Builder Toolbar—>Sources tab—>Functions panel, expand the Operators node, and choose the ne function.)

When any functional relationship is involved besides equality, you must chose from the list of functions available in the Builder Toolbar—>Sources tab—>Functions panel. At that point you are applying a filter of your choice. It is very important to choose the function before you map the elements. Most of the Data View Builder functions are standard XML query language functions supported by the W3C. For related information about using functions, see the Functions Reference.

Note: You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used. When Automatic Type Casting is in effect, 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.

Using Constants and Variables in Functions

Instead of choosing an existing element/attribute as a parameter value, you can use one of these methods to specify that a constant value should be used instead of a data element from a source schema.

- Click the Query Parameter Navigation panel. If you wish to add a new variable, type the variable name in the available text box. Click Add. The new variable appears in the Query Parameter tree. Double click the new variable to use it as a parameter value. You can change the value each time your run the query. (For details on defining query parameters, see Query Parameters: Defining, which includes a list of supported data types for query parameters in Table 2-2.)

- Click the Constant Navigation panel. If the constant already exists in the Constant tree, double click the constant you want to use as a parameter in a function or drag and drop the constant to the appropriate row in the Condition column.(For details on defining constants, see Constants.)

Enabling and Disabling Conditions

Conditions are displayed in the Design view on the Conditions tab. If you want to test your query without one or more of the conditions you have set, but still keep the condition configured for possible later use, you can disable a condition. Conversely, you can enable a disabled condition.

To enable or disable a condition, click the Enabled box to the left of the Condition. (See Figure 3-4.) When the box is checked, the condition is used when the query is generated; when the box is blank, the condition is not used in the generated query.

Figure 3-4 Enabling or Disabling a Condition

Removing Conditions

Conditions are displayed in the Design view on the Conditions tab. You can change your mind and remove a condition by selecting the row or cell that contains it and then clicking the trashcan button. (See Figure 3-5.)

Figure 3-5 Removing a Condition

Adding or Deleting Parameters in a Condition Statement

To add or delete a 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 trash can or use the options on the Edit menu to modify the condition statement.

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

Showing or Hiding Data Types

You can show or hide data types on all source and target elements/attributes in schema windows. Select View—>Data Types to display the data type of any source or target element/attribute, as well as required function parameter types. (An "X" next to the Data Types option on the View menu indicates that it is on.)

Using Automatic Type Casting

You can use automatic type casting to ensure that input parameters used in functions and mappings are appropriate to the function in which they are used.

Note: For a complete reference showing how Liquid Data transforms source element/attribute data types to data types of target elements/attributes, see the Type Casting Reference.

Select Automatic Type Casting on the Query menu to ensure that Liquid Data will assign (cast) a new data type when the source node data type does not match the mapped target node data type, and the source node is eligible to be type cast to the target node data type. (An "X" 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.

Type | Promoted Type
--- | ---
byte | short
short | int
int | long
long | integer
integer | decimal
decimal | float
float | double

If the type mismatch requires casting in the reverse order, the server does not attempt type casting. In this case, Liquid Data attempts to type cast but the results may be unpredictable. For 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. If the input function type is xs:double or xs:float and the required input type is xs:integer or xs:byte, Liquid Data tries to type cast successfully, but there may be unpredictable rounding or truncating. 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.

Exceptions to Automatic Type Casting

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

Type casting does not apply to function parameters (as well as target schema elements/attributes) that require these data types:

- xsext:item

- xsext:anyValue

- xsext:anyType

- Any other data type that cannot be cast

Automatic type casting does not succeed in all cases. If the source data is not compatible with the data type of the target node, automatic type casting will not improve the query results. For example, mapping a date to a numeric type may not produce useful results if the data is not relevant. You may not see an error on a type mismatch until the Liquid Data Server tries to run the query.