Contents for 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