Building Queries and Data Views

Introduction

Data View Builder Overview

Benefits of the Data View Builder

How the Data View Builder Works

Key Concepts of Query Building

Data Sources

Source and Target Schemas

Queries and Query Joins, Unions, Aggregates, and Functions

Joins

Unions

XQuery Functions

Query Parameters

Constants

Stored Queries

Ad Hoc Queries

Query Plans

How This Book is Organized

Next Steps

Data View Builder GUI Reference

Starting the Data View Builder

Data View Builder GUI Tour

Design Tab

Overview Picture of Design Tab Components

1. Menu Bar for the Design Tab

2. Toolbar for the Design Tab

3. Builder Toolbar

4. Source Schemas

5. Target Schema

6. Conditions Tab

7. Mappings Tab

8. Sort By Tab

9. Status Bar

Optimize Tab

Test Tab

Overview Picture of Test Tab Components

1. Menu Bar for the Test Tab

2. Toolbar for the Test Tab

3. Builder-Generated XQuery

4. Query Parameters: Submitted at Query Runtime

5. Query Results - Large Results

6. Run Query

7. Result of a Query

Working With Liquid Data Projects

Using Schemas Saved With Projects

Save Target Schema to Repository

Next Steps: Building and Testing Sample Queries

Data Sources

Relational Databases

XML Files

Web Services

Application Views

Data Views

SQL Calls

Delimited Files

Schemas and Namespaces in Liquid Data

Source and Target Schemas

Source Schemas

Using Source Schemas Multiple Times in Constructing Queries

Target Schemas

Guidelines for Working With Target Schemas

Managing Target Schemas

Using Schemas Saved With Projects

Schema Import Resolution Rules

Understanding XML Namespaces

XML Namespace Overview

Predefined Namespaces in XQuery

Other XML Namespace References

Using XML Namespaces in Liquid Data Queries and Schemas

Namespace Declarations in XQuery Prolog

Defining Namespaces in Target Schema

Data Sources that Require Namespace Declarations

Migrating Liquid Data 1.0 Queries

Building Queries

Defining Query Requirements

Examples Set-up

Using the Function Editor

Managing Query Components

Data Sources

Creating and Using Constants

Using Constants with Functions

Creating and Using Query Parameters

Using XQuery Functions

Mapping Elements to Functions

Working With Source and Target Schema Elements

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

Mapping to Target Schemas

Mapping Elements and Attributes Between Source and Target Schema

Complex Element Mappings

Expanding Mapped Complex Elements

Removing Mappings

Modifying Target Schemas

Managing Target Schema Properties

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

Setting Query Conditions

Working With the Conditions Panel

Enabling or Disabling Conditions

Removing Conditions

Editing Conditions

Understanding Condition Scoping

Where Scope Applies

Setting Condition Scope

Scope Recursion Errors

Scoping Example

Task Flow Model for Advanced View Manual Scoping

Sorting Query Results

Using Existential Condition Checking in Queries

An Existential Example

Using Automatic Type Casting

Automatic Type Casting Transformations

Exceptions to Automatic Type Casting

Running, Saving, and Deploying Queries

Test Mode

Viewing a Generated Query

Editing a Generated Query

Running a Query

Stopping a Running Query

Specifying Large Results Sets

Specifying Query Parameters

Setting and Changing Query Parameters

Saving a Query

Security Considerations

Query Naming Conventions

Using the stored_queries Folder

Caching Query Results

Steps to Save a Query to the Repository

Deploying a Query

Deploy Query Command

Saving the Current Schema and Current Query

Deploying Your Query

Deploying a Stored Query with a Data View

Analyzing and Optimizing Queries

Query Analysis

Viewing the Query Plan

Getting Information on the Query

Factors in Query Performance

Optimizing Queries

Source Order Optimization

Example of Source Order Optimization

Optimization Hints

Determining When Hints Are Needed

Using the Liquid Data Built-in Optimizer

Using Parameter Passing Hints (ppleft or ppright)

Using Merge Hints

Using Data Views

The Enterprise and the Data View

Understanding Data Views

A Data View Use Case

Simple and Parameterized Data Views

Using Data Views as Data Sources

Creating a Data View

Creating and Saving the Query to the Liquid Data Repository

Configuring a Data View Data Source Description

Adding a Data View as a Data Source

Creating a Parameterized Data View

Data View Query Samples

Using Complex Parameter Types in Queries

Understanding Complex Parameter Types

A CPT Use Case

Understanding CPT Schema and Data

Sample CPT Schema

Sample XML Data Stream

Notes on Hand-Crafting CPT XQueries

Unique Namespace

XQuery of type element Declaration

Creating a Complex Parameter Type

Step 1. Create a CPT Schema

Step 2. Create Your Runtime Source

Step 3. Define Your CPT in the Administration Console

Step 4. Build Your Query

Step 5. Run Your Query

Complex Parameter Type Query Samples

Accessing SQL Calls: Stored Procedures and SQL Queries

Defining Stored Procedures to Liquid Data

To Define Stored Procedures to Liquid Data

SQL Call Description File

Basic Structure

Type Definitions

Function Definitions

Schema Definition File for SQL Call Description File

Element and Attribute Reference for SQL Call Description File

Supported Datatypes

Rules for Specifying SQL Call Description Files

Rules for Element and Attribute Names

Rules for Procedure Names Containing a Semi-Colon

Rules and Examples of <type> Declarations to Use in the <function> return_type Attribute

Example 1: Type Definition with No Return Value

Example 2: Type Definition with Simple Return Value

Example 3: Type Definition for Complex Row Set Type

Example 4: Type Definition with Complex Return Value

Example 5: Type Definition with Simple Return Value and Two Row Sets

Rules for the mode Attribute output_only <argument> Element

Rules for Transforming the Function Signature When Hand Writing an XQuery

Namespace Declaration

Function Transformation

Sample SQL Call Description Files

DB2 Simple input_only, output_only, and input_output Example

Oracle Cursor Output Parameter Example

DB2 Multiple Result Set Example

Oracle Cursor as return_value

Oracle SQL Statement With Subquery

Stored Procedure Support by Database

Oracle

Microsoft SQL Server

Sybase

IBM DB2

Informix

Using Stored Procedures in Queries

Define Stored Procedures to Liquid Data

Example: Defining and Using a Customer Orders Stored Procedure

Business Scenario

View a Demo

Step 1: Create the Stored Procedure in the Database

Step 2: Create the SQL Call Description File

Step 3: Specify the SQL Call Description File in the Liquid Data Console

Step 4: Open the Data View Builder to See Your Stored Procedures

Step 5: Use the Stored Procedure in a Query

Step 6: Run the Query