Liquid Data by Example

Samples Installed with Liquid Data

This chapter describes samples installed with Liquid Data which are arranged in the following sections:

• Simple Liquid Data Queries
• Complex Parameter Type (CPT) Sample Queries
• Data View Sample Queries
• Application View Sample Queries
• Miscellaneous Samples
• Custom Functions (DB-UDF) Sample Query
• DB-Web Service Sample Query
• SQL_Call Sample Query
• EJB API Sample

Sample queries can be found at:

<WL_HOME>/samples/liquiddata/

In the following sub-directories:

• buildQuery contains all sample queries other than EJB programming examples and the retail sample application
• ejbAPI provides an EJB programming example
• SampleApp contains the Retail Sample Application, described in Understanding the Avitek Customer Self-Service Sample Application.

 

---

Simple Liquid Data Queries

This section describes several types of simple queries you can run and/or recreate in the Data View Builder.

DB-XML Sample Query

This section includes the following topics related to the DB-XML sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

The sample order query demonstrates how to use Liquid Data to create an integrated view that shows the connection of two different Enterprise Information Systems (EIS), a database, and XML. Creating an integrated view provides the ability to seamlessly access separate EISs using a single query.

Business Scenario

A wireless service provider uses a relational database system to manage its customer and order information. Recently it purchased a broadband service from another company. The broadband customer and order information is managed by a non-DBMS system, e.g., XML file system. To provide an integrated customer service system, we use the Liquid Data engine to seamlessly access customer order information across different types of Enterprise Information Systems (EIS).

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-xml/e2e-order.qpr 

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the results. Results include:
• Last name: KAY_1
• Two wireless orders: 1000 and 2000
• Two BroadBand orders: 1000 and 1500

If You Want to Recreate the Query

You can find a detailed tutorial describing how to create a similar query in the Liquid Data Getting Started guide.

References

You can find the Wireless database schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/WIRELESSP.sql 

You can find the BroadBand XML schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/b-co.xsd 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/customerOrderReport.xsd

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/order.xq

Data Transformation Sample Query

This section includes the following topics related to the Data Transformation Sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This query demonstrates the use of functions for:

• Simple data transformations such as to_lower_case, to_upper_case and concatenation
• Aggregation

Business Scenario

A telecom service provider has information on its BroadBand customers stored in one database system, represented in Liquid Data by a schema called BroadBand. Information about its wireless customers is in a different database system and is represented by a schema called Wireless.

The problem to be solved is that the customer representative needs to find out the average spending for customers who use both Broadband and Wireless services.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.

In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/dataTransform/data.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result. For CUSTOMER_1 results are:
• john_b_1
• KAY_1
• 1375
• KAY_1, JOHN_B_1

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

There are several ways to effect data transformations. The approach taken in this example is to transform a data element to the point where it is ready to be mapped to the target schema and then do the mapping.

All the data transformation functions shown in this sample are available under in the XQuery Functions section of the Toolbox. The term srcval refer to the input side of an XQuery function.

There are six parts to the data transformation sample:

• Setting Up the Project
• Converting a String to Lower Case
• Converting a String to Upper Case
• Combining Two Strings (Concatenation)
• Determining Average Sale Price

Setting Up the Project

Create a new project.
Move the following data sources into the work area:

Relational Databases:

• PB-BB (BroadBand orders RDBMS)
• PB-WL (Wireless orders RDBMS)
Set the target schema to dta.xsd
Click on the Toolbox tab.
Select Query Parameters.
In the name field enter custID.
Assign a type of xs:string. Then click Create.
Create a join (eq) between your new query parameter and the CUSTOMER_ID field in the BroadBand data source [PB-BB]:.

Join Element	Join Element
[Query Parameter] custID	[PB-BB]/db/CUSTOMER/CUSTOMER_ID

Create a join (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-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID
[PB-BB]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER/CUSTOMER_ID
[PB-WL]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER_ORDER/CUSTOMER_ID

Converting a String to Lower Case

Click XQuery Functions in the Toolbox.
Move the XQuery string function xf:lower-case into the work area.
Transform the Broadband [PB-BB] First Name element to lower case:

Source:[PB-BB]/db/	Target: String Function
CUSTOMER/FIRST_NAME	xf:lower-case/srcval

Map the output of the lower-case function to the target schema:

Source: String Function	Target: [dta.xsd]/results/
xf:lower-case/result	result/FIRST_NAME

Close xf:lower-case.

Converting a String to Upper Case

Move the XQuery string function xf:upper-case into the work area.
Transform the Broadband [PB-BB] Last Name element to upper case:

Source:[PB-BB]/db/	Target: String Function
CUSTOMER/LAST_NAME	xf:/upper-case/srcval

Map the output of the xf:upper-case function to the target schema:

Source: String Function	Target: [dta.xsd]/results/
xf:uppercase/result	result/LAST_NAME

Close xf:upper-case.

Combining Two Strings (Concatenation)

In this section you can create a string containing a concatenation of first and last name, last name first. This involves using the two XQuery concatenate functions you just moved into the work area. Once the string is built up, you can map it into the target schema.

Move two instances of the XQuery string concatenate function into the work area. They will be automatically labeled xf:concat and xf:concat2.
Click on the Constants button in the left pane. In the string field enter ", " (do not type the quotes, only the comma followed by a space).
Drag the BroadBand LAST_NAME element [PB-BB] to the first input field of the first concatenation function:

Source:[PB-BB]/db/	Target: String Function
CUSTOMER/LAST_NAME	[xf:concat]/operand1

Drag the string constant ", " to the second input field of the first concatenation function:

Source: Constant	Target: String Function
,	[xf:concat]/operand2

Populate the input fields of the second concatenation function with the output result of the first concatenation function and [PB-BB] FIRST_NAME.

Source: String Function	Target: String Function
[xf:concat]/result	[xf:concat]/operand1

.

Source:[PB-BB]/db/	Target: String Function
CUSTOMER/FIRST_NAME	[xf:concat]/operand2

Map the second concatenation function to the target schema:

Source: String Function	Target: [dta.xsd]/results/
[xf:concat]/result	result/FULL_NAME

Close both concatenation functions.

Determining Average Sale Price

You first need to get a total for both Wireless and BroadBand sales.

Move two instances of the XQuery aggregate sum function into the work area. They will be automatically labeled xf:sum and xf:sum2.
Move the BroadBand TOTAL_ORDER_AMOUNT element to the input field of the first sum and Wireless TOTAL_ORDER_AMOUNT to the input field of the second sum function:

Source:[PB-BB]/db/	Target: Aggregate Function
[PB-BB]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT	[xf:sum]/srcval (BroadBand)
[PB-WL]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT	[xf:sum]/srcval (Wireless)

Move an instance of the XQuery numeric plus (+) function into the work area.
Add together the TOTAL_ORDER_AMOUNT sums of Wireless and BroadBand:

Source: Aggregate Function	Target: Numeric Operator Function
[xf:sum]/result (Wireless)	[+]/operand1
[xf:sum]/result (BroadBand)	[+]/operand2

Move an instance of the XQuery numeric division (DIV) function into the work area.
Move the result of the addition of Wireless and BroadBand sales to the first DIV operand:

Source: Numeric Operator Function	Target: Numeric Operator Function
[+]/result (total order amount)	xf:div/operand1

Close instances of plus [xf:+] and sum [xf:sum].

Next you want to get the total number of sales for Wireless and BroadBand combined.

Move two instances of the XQuery aggregate xf:count function into the work area. They will be automatically labeled xf:count and xf:count2.
Move the BroadBand ORDER_ID element to the input field of the first xf:count function and Wireless ORDER_ID to the input field of the second xf:count function.

Source:[PB-BB]/db/	Target: Aggregate Function
[PB-BB]/db/CUSTOMER_ORDER/ORDER_ID	[xf:count]/srcval (BroadBand)
[PB-WL]/db/CUSTOMER_ORDER/ORDER_ID	[xf:count]/srcval (Wireless)

Move an instance of the XQuery numeric plus (+) function into the work area.
Add together the ORDER_ID count totals of Wireless and BroadBand:

Source: Aggregate Function	Target: Numeric Operator Function
[xf:count]/result (Wireless)	[+]/operand1
[xf:count]/result (BroadBand)	[+]/operand2

Move the result of the addition of Wireless and BroadBand sales counts to the second DIV operand:

Source: Numeric Operator Function	Target: Numeric Operator Function
[+]/result (total order amount)	xf:div/operand2

Map the output of the XQuery division function to the target schema:

Source: String Function	Target: [dta.xsd]/results/
xf:div/result	result/average

Enter Test mode.
Supply CUSTOMER_1 for the custID parameter.
Run your query. Results are:
• john_b_1
• KAY_1
• 1375
• KAY_1, JOHN_B_1

References

You can find the wireless data base schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql 

You can find the BroadBand XML schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/BROADBANDP.sql

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/dta.xsd 

DB-DB Sample Query

This section includes the following related to the DB-DB sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This query demonstrates how to use Liquid Data to construct a query that integrates data from two distributed heterogeneous database sources.

Traditionally, an application developer needs to write a set of data access methods and some join coding to complete the information extraction and assembly. This example demonstrates how you can use a single XQuery statement to declaratively accomplish the same task in much cleaner way.

Business Scenario

A service provider has customer information stored in one database server. All the promotion plans are managed by another database server. This sample query illustrates how to use Liquid Data to extract qualified promotion information for a customer based on the state the customer lives in.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-db/e2e-promotion.qpr

Click the Test tab. (This shows the generated query statement.)
Click the "Run Query" button and view the result.

Results should include:

• Last name: KAY_1
• State: TX
• Promotion name: WIRELESS UPSELL
• Price: $49.99

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

Create a new project.
Move the following Relational Database data source schemas into the work area:
• PB-WL (Wireless)
• PB-CR (Relationship Management)
Set promotionData.xsd from the repository schema directory as the target schema and expand the schema.
Map the following elements from the Wireless customer data source (PB-WL) to the target schema:

Source: [PB-WL]/db/	Target: [promotionData.xsd]/PromotionInfo/Customer_promotion
CUSTOMER/FIRST_NAME	CUSTOMER/FIRST_NAME
CUSTOMER/LAST_NAME	CUSTOMER/LAST_NAME
CUSTOMER/CUSTOMER_ID	CUSTOMER/CUSTOMER_ID
CUSTOMER/STATE	CUSTOMER/STATE

Map the following elements from the customer relationship data source (PB-CR) to the target schema:

Source: [PB-CR]/db/	Target: [promotionData.xsd]/PromotionInfo/Customer_promotion
PROMOTION/PROMOTION_NAME	PROMOTION_PLAN/PROMOTION_NAME
PROMOTION_PLAN/PLAN_NAME	PROMOTION_PLAN/PLAN_NAME
PROMOTION_PLAN/FROM_DATE	PROMOTION_PLAN/FROM_DATE
PROMOTION_PLAN/TO_DATE	PROMOTION_PLAN/TO_DATE
PROMOTION_PLAN/PRICE	PROMOTION_PLAN/PRICE

In the Toolbox, click on the Constants button, enter CUSTOMER_1 in the string field.
Create equal joins (eq) between the following pairs of elements by dragging one element over the other:

Join Element	Join Element
[PB-CR]/db/PROMOTION/PROMOTION_NAME	[PB-CR]/db/PROMOTION_PLAN/PROMOTION_NAME
[PB-CR]/db/PROMOTION/STATE	[PB-WL]/db/CUSTOMER/STATE

Map the CUSTOMER_1 constant to the Wireless data source CUSTOMER_ID element.

Source: Constant	Target: [PB-WL]/db/
CUSTOMER_1	CUSTOMER/CUSTOMER_ID

Enter Test mode and run your query.

Results should include:

• Last name: KAY_1
• State: TX
• Promotion name: WIRELESS UPSELL
• Price: $49.99

References

You can find the Wireless data base schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql 

You can find the CRM database schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/CRMP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/promotionData.xsd 

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/promotion.xq

 

---

Complex Parameter Type (CPT) Sample Queries

DB-CPT Sample Query

This section includes the following topics related to the DB-CPT sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Create a Query that Uses a Complex Parameter Type (CPT)
• References

What This Query Demonstrates

This example demonstrates use of Liquid Data to create a single query spanning two Enterprise Information Systems (EIS), a database, and a complex parameter type (CPT).

Business Scenario

A CRM service provider uses a relational database system to manage its promotion plan. A CRM CPT has the promotion plan name for a given state and wishes to extract the details of one or more matching plan name from the database. We use the Liquid Data engine to seamlessly access CRM information across different types of Enterprise Information Systems (EIS).

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-cpt/cptSample.qpr

Click the Test tab. (This shows the generated query statement.)
Specify the location of the CPT sample XML stream for the sample parameter:

<WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/crm-p-cptSample.xml

Click the Run Query button and view the result. Five promotion plans (CA, TX, WA, AZ, NV) are returned.

If You Want to Create a Query that Uses a Complex Parameter Type (CPT)

You can find a detailed example of creating a query that uses CPTs in Example 7: Complex Parameter Type (CPT).

References

You can find the CRM database schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/CRMP.sql 

You can find the CPTSAMPLE XML schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/crm-p-cptSample.xsd

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/cpt_sample.xsd 

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/crm_cptSample.xq

DB-CPTCO Sample Query

This section includes the following topics related to the DB-CPTCO sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Create a Query That Use a Complex Parameter Type
• References

What This Query Demonstrates

This sample order query demonstrates how to use Liquid Data to create an integrated view that shows the connection of two different Enterprise Information (EIS) Systems, a database, and a complex parameter type (CPT).

Business Scenario

A BroadBand service provider uses a relational database system to manage its customer and order information. It received a new order for a given customer via XML mapped to a complex parameter type (CPT). The XML for CPT consists of a customer id along with one or more new orders specifying the price and quantity that the customer is ordering.

The objective is to accept or reject orders based on determining the total outstanding balance once the existing outstanding (unpaid) balance and the total value of the new order are added together.

If the result is above the limit, then the routine outputs the statement Order Rejected. Otherwise the statement Order Accepted is output.

It is noteworthy that these results can be obtained only after the Liquid Data engine accesses customer order information through separate EISs using a single query.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-cptco/coCPTSample.qpr

Click the Test tab. (This shows the generated query statement.)
Specify the location of the CPT sample XML stream. Navigate to:

<WL_HOME>/samples/domains/liquiddata/ldrepository/xml_files/coCptSample2.xml

Click the Run Query button to view the result. Results include:
• Last name: KAY_1
• Open orders: 150000
• New order amount: 40000
• Status: Order Accepted

If You Want to Create a Query That Use a Complex Parameter Type

You can find a detailed example of creating a query that uses CPTs in Example 7: Complex Parameter Type (CPT).

References

You can find the CRM database schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/CRMP.sql

You can find the CPTSAMPLE XML schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/crm-p-cptSample.xsd

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/cpt_sample.xsd 

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/crm_cptSample.xq

 

---

Data View Sample Queries

Simple Data View Sample Query

This section includes the following topics related to the simple data view sample query:

• What This Query Demonstrates
• How To Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This query demonstrates how to add data views (created with the Data View Builder) to the Liquid Data Server repository as data sources. Once configured (as shown in the following example), Data Views become data sources for any Data View Builder client that connects to the server.

Business Scenario

It's getting close to the end of the quarter. The Accounts Receivable department wants to collect on receivables. An "outstanding order" query has been generated by the MIS team in the Sales Department. It provides a listing of all the outstanding orders with unpaid balance.

However, only the order id is included in the list. The MIS team in the Account Receivable department will turn this query into a Data View and design a light-weight ad hoc query to find the name and address of the outstanding accounts with more than $1,000 in amount based on the order ID reported in the Data View source.

How To Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/view/viewSample.qpr 

Click the Test tab. (This shows the generated query statement.)
Click the run query button and view the result. Results include:
• Broadband order amount: 1500
• Wireless order amount: 2000

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

Create a new project.
Move the following data sources into the work area:

Relational Databases:

• PB-BB (broadband orders RDBMS)
• PB-WL (wireless orders RDBMS)

Data Views:

• V_SRC
Set the target schema to viewtarget.xsd
Map the following elements from the Wireless customer data source (PB-WL) to the target schema:

Source: [PB-WL]/db/	Target: [viewtarget.xsd]/ViewResult/
CUSTOMER/FIRST_NAME	wireless/FIRST_NAME
CUSTOMER/LAST_NAME	wireless/LAST_NAME
CUSTOMER/CUSTOMER_ID	wireless/CUSTOMER_ID
CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT	wireless/TOTAL_ORDER_AMOUNT

Map the following elements from the BroadBand customer data source (PB-BB) to the target schema:

Source: [PB-BB]/db/	Target: [viewtarget.xsd]/ViewResult/
CUSTOMER/FIRST_NAME	broadband/FIRST_NAME
CUSTOMER/LAST_NAME	broadband/LAST_NAME
CUSTOMER/CUSTOMER_ID	broadband/CUSTOMER_ID
CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT	broadband/TOTAL_ORDER_AMOUNT

Map the following elements from the data view orders data source (V_SRC) to the target schema:

Source: [V_SRC]/results/result/	Target: [viewtarget.xsd]/ViewResult/
broadband/order/ORDER_ID	broadband/ORDER_ID
wireless/order/ORDER_ID	wireless/ORDER_ID

Create a join (eq) between the following pairs of elements by dragging one element over the other:

Join Element	Join Element
[PB-BB]/db/CUSTOMER/CUSTOMER_ID	[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER/CUSTOMER_ID
[PB-WL]/db/CUSTOMER/CUSTOMER_ID	[PB-WL]/db/CUSTOMER/CUSTOMER_ORDER/CUSTOMER_ID
[V_SRC]/results/result/broadband/order/ORDER_ID	[PB-BB]/db/CUSTOMER_ORDER/ORDER_ID
[V_SRC]/results/result/wireless/order/ORDER_ID	[PB-WL]/db/CUSTOMER_ORDER/ORDER_ID

In the Toolbox choose the greater than function (xf:gt) from Comparison Operators.
Click on XQuery Functions. Under Comparison Operators locate the gt function. Move two instances into the work area. These will automatically be labeled xf:gt and xf:gt2.
Click on the Constants button in the left pane, enter 1000 in the number field. Then click Ok.
Create a comparison that determines whether BroadBand (PB-BB) CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT is greater than (gt) 1,000. Then associate the GT test result with TOTAL_ORDER_AMOUNT.

Source	Target: Comparison Function
[PB-BB]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT	[GT]/anyValue1
[CONSTANT]/1000	[GT]/anyValue2
[GT]/result	[PB-BB]/db/CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT

Close the GT function whose results you just mapped.
Using the second GT function repeat Step 11 using the Wireless (PB-WL) CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT field and the 1000 constant.
Enter Test mode and run your query. Results include:
• Broadband order amount: 1500
• Wireless order amount: 2000

References

You can find the Wireless database schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql 

You can find the BroadBand database schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/BROADBANDP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/viewtarget.xsd

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/viewSample.xq

Parameterized Data View Sample Queries

This section includes two parameterized data view sample queries.

pviewSample

The following topics related to the pviewSample parameterized data view sample query:

• What This Queries Demonstrates
• How to Run the Query
• If You Want to Create a Query That Uses Parameterized Views
• References

What This Queries Demonstrates

This parameterized sample query demonstrate how to construct and use a parameterized view.

Orders are aggregated from two different data sources: an EIS system (via J2EE connector architecture and a WebLogic Application View), and a relational data source.

The order view is constructed as a parameterized view with CUSTOMER_ID being the input parameter. Once the data view is constructed and configured, it can be used to construct other queries; for example, you can create a query that returns customer information and all detail order information from the parameterized view.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open either of the following project files:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/parameterized-view/pviewSample.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result for the CUSTOMER_2 customer ID. Results include:
• Customer ID: CUSTOMER_2
• Last name: KAY_2
• Three BroadBand orders: 1000, 1500, 2000
• Three Wireless orders: 1000, 2000, 4000

If You Want to Create a Query That Uses Parameterized Views

You can find a detailed example describing how to create a parameterized query in Creating a Parameterized Data View in Building Queries and Data Views.

References

You can find the sample Wireless database schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql

You can find the sample BroadBand database schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/BROADBANDP.sql 

You can find the sample target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/pviewsrc.xsd

and

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/pviewsrc.xsd/pviewSample.xsd 

You can find the sample query statements at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/pviewsrc.xq

and

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/pviewSample.xq 

pviewSample1

The following topics related to the pviewSample1 parameterized data view sample query:

• What This Queries Demonstrates
• How to Run the Query
• If You Want to Create a Query That Uses Parameterized Views
• References

What This Query Demonstrates

This parameterized sample query demonstrate how to construct and use a parameterized view.

Orders are aggregated from two different data sources: a web service and a relational data source.

The order view is constructed as a parameterized view with CUSTOMER_ID being the input parameter. Once the data view is constructed and configured, it can be used to construct other queries; for example, you can create a query that returns customer information and all detail order information from the parameterized view.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open either of the following project files:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/parameterized-view/pviewSample1.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result for the CUSTOMER_2 customer ID. Results include:
• Customer ID: CUSTOMER_2
• Last name: KAY_2
• Three BroadBand orders: 1000, 1500, 2000
• Two Wireless orders: 1000, 2000

If You Want to Create a Query That Uses Parameterized Views

You can find a detailed example describing how to create a parameterized query in Creating a Parameterized Data View in Building Queries and Data Views.

References

You can find the sample Wireless database schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql

You can find the sample BroadBand database schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/BROADBANDP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/pviewsrc.xsd

and

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/pviewsrc.xsd/pviewSample.xsd 

You can view the query statements at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/pviewsrc1.xq

and

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/pviewSample1.xq 

 

---

Application View Sample Queries

DB-AppView (Three Data Source) Sample Query

This section includes the following topics related to the DB-AppView sample query that uses three data sources:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• Reference

What This Query Demonstrates

This sample illustrates how to use LiquidData to extract customer information from three heterogeneous data sources: a JCA/Application View, an XML file, and a relational database.

Business Scenario

Customer information is stored in XML; customer orders can only be accessed through an EIS system (via J2EE Connector Architecture and WebLogic Application View); and promotion information is stored in a relational database. Traditionally, an application developer needs to write a set of data access methods and some application logic to complete the information extraction and assemble the result. Using Liquid Data, a single XQuery statement is used to accomplish this task.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-appview-xml/threeds.qpr 

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button to view the result. Results include:
• First name: JOHN_1
• Last name: KAY_1
• Two customer orders in the amounts of 1000 and 2000
• One $49.99 promotion plan called Family Holiday Connect

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

Create a new project.
Move the following data sources into the work area:

XML Files:

• XM-WL-C (wireless customers)

Relational Databases:

• PB-CR (promotion plan RDBMS)

Application Views:

• AV-WL/getCustomerOrder (wireless customer orders)
Set the target schema to threeds.xsd
Map the following elements from the Wireless customer XML data source (XM-WL-C) to the target schema:

Source: [XM-WL-C]/db/	Target: [threeds.xsd]/CUSTOMERINFO/
CUSTOMER/FIRST_NAME	CUSTOMER/FIRST_NAME
CUSTOMER/LAST_NAME	CUSTOMER/LAST_NAME
CUSTOMER/CUSTOMER_ID	CUSTOMER/CUSTOMER_ID

Map the following elements from the Wireless orders application view (AV-WL) to the target schema:

Source: [AV-WL]/getCustomerOrder/	Target: [threeds.xsd]/CUSTOMERINFO/
ns:Rows/Row/TOTAL_ORDER_AMOUNT	ORDERS/ORDER/TOTAL_ORDER_AMOUNT
ns:Rows/Row/ORDER_ID	ORDERS/ORDER/ORDER_ID
ns:Rows/Row/SHIP_METHOD	ORDERS/ORDER/SHIP_METHOD

Map the following elements from the Relational promotion plan data source (PB-CR) to the target schema:

Source: [PB-CR]/db/	Target: [threeds.xsd]/CUSTOMERINFO/
PROMOTION/PROMOTION_NAME	PROMOTIONPLANS/PROMOTIONPLAN/PROMOTION_NAME
PROMOTION_PLAN/FROM_DATE	PROMOTIONPLANS/PROMOTIONPLAN/FROM_DATE
PROMOTION_PLAN/TO_DATE	PROMOTIONPLANS/PROMOTIONPLAN/TO_DATE
PROMOTION_PLAN/PRICE	PROMOTIONPLANS/PROMOTIONPLAN/PRICE

Click on the Toolbox tab.
Click Constants. Enter CUSTOMER_1 in the String field.
Create joins (eq) between the following pairs of elements by dragging one element over the other:

Join Element	Join Element
[XM-WL-C]/db/CUSTOMER/CUSTOMER_ID	[AV-WL:getCustomerOrder]/ns1:Input/CUSTOMER_ID
[XM-WL-C]/db/CUSTOMER/STATE	[PB-CR]/db/PROMOTION/STATE
[PB-CR]/db/PROMOTION/PROMOTION_NAME	[PB-CR]/db/PROMOTION_PLAN/PROMOTION_NAME

Map the CUSTOMER_1 constant to the Wireless Customer [XM-WL-C] CUSTOMER_ID field:

Source: Constant	Target: [XM-WL-C]/db/
CUSTOMER_1	CUSTOMER/CUSTOMER_ID

Create the following greater than (GT) tests which will filter out orders less than $1,000:
Run your query. Results should include:

• First name: JOHN_1
• Last name: KAY_1
• Two customer orders in the amounts of 1000 and 2000
• One $49.99 promotion plan called Family Holiday Connect

Reference

You can find the wireless data base schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql 

You can find the CRM database schema at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/CRMP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/threeds.xsd 

DB-AppView (Two Data Source) Sample Query

This section includes the following topics related to the DB-AppVIew sample query that uses two data sources:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

The sample for customer care query illustrates how to use Liquid Data to extract customer order handling information from two data sources:

• a JCA Application View
• a relational database containing customer information

Business Scenario

Due to historical reasons a telecom company has two separate order management systems: one for wireless service and the other for broadband. The wireless customer order information can only be accessed as an EIS system (via J2EE Connector Architecture and WebLogic Application View), and the broadband customer order information can only be accessed through its relational database.

The customer ID is the same across two systems. Traditionally, an application developer needs to write a set of data access methods and some application logic to complete the information extraction and assembly. Using Liquid Data, we demonstrate the use of one single Xquery statement to declaratively accomplish the same task.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-appview/db_appview.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result. Results include:
• First name: JOHN_1
• Last name: KAY_1
• Orders: Six orders (three for 200; three for 300)

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

Create a new project.
Move the following data sources into the work area:

Relational Databases:

• PB-BB (broadband customer RDBMS)

Application Views:

• AV-WL/AV-WL/getCustomerByFullName (wireless customer orders)
Set the target schema to customerOrders.xsd
Click the Toolbox tab.
Click the Constants button, enter JOHN_1 in the string field.
Create a join (eq) between the following elements by dragging one element over the other
Source: Constant	Target: [AV-WL]:getCustomerByFullName/
JOHN_1	ns3:Input/FIRST_NAME
:
Enter KAY_1 in the constant string field.
Create an equal join (eq) between the following elements by dragging one element over the other:

Source: Constant	Target: [AV-WL]:getCustomerByFullName/
KAY_1	ns3:Input/LAST_NAME

Map the following elements from the AV-WL data source to the target schema:

Source: [AV-WL]:getCustomerByFullName/	Target: [customerOrders.xsd]/customers/
ns2:Rows/Row/CUSTOMER_ID/FIRST_NAME	customer/first_name
ns2:Rows/Row/CUSTOMER_ID/LAST_NAME	customer/last_name
ns2:Rows/Row/CUSTOMER_ID/CUSTOMER_ID	customer/id

Create a join [eq] between the following elements by dragging one element over the other:

Join Element	Join Element
[AV-WL]/getCustomerByFullName/ns2:Rows/Row/CUSTOMER_ID	[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER/CUSTOMER_ID
[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER/ORDER_ID	[PB-BB]/db/CUSTOMER/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID

Map the following elements from the BroadBand customer relation data source [PB-BB] to the target schema:

Source: [PB-BB]/db/CUSTOMER/	Target: [customerOrders.xsd]/customers/
CUSTOMER_ORDER/ORDER_DATE	orders/order/date
CUSTOMER_ORDER/ORDER_ID	orders/order/id
CUSTOMER_ORDER_LINE_ITEM/PRICE	orders/order/amount

In the Constants string field enter OPEN.
Map the OPEN constant to the BroadBand orders [PB-BB] CUSTOMER_ORDER_LINE_ITEM by dragging one element over the other
Source: Constant	Target: [PB-BB]/db/CUSTOMER/
OPEN	CUSTOMER_ORDER_LINE_ITEM/STATUS
:
Enter Test mode and run your query. Results include:
• First name: JOHN_1
• Last name: KAY_1
• Orders: Six orders (three for 200; three for 300)

References

You can find the BroadBand database schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/BROADBANDP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/customerOrders.xsd

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/db_appview.xq

 

---

Miscellaneous Samples

This section contains several samples, including:

• Stored Procedure Sample Query
• Custom Functions (DB-UDF) Sample Query
• DB-Web Service Sample Query
• EJB API Sample

Stored Procedure Sample Query

This section includes the following topics related to the stored procedure sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Create a Query That Uses Stored Procedures
• References

What This Query Demonstrates

This query demonstrates how to use RDBMS stored procedures as Liquid Data data sources. Once configured a RDBMS stored procedure becomes available as a function to any Data View Builder client that connects to the Liquid Data Server.

Business Scenario

For example, the MIS Department of a wireless service provider uses a database management system to manage its customer and order information. The engineers have already developed a full set of stored procedures with embedded business logic.

Liquid Data allows them to treat a stored procedure as a regular function that users can build queries on top of it. All the existing investment in stored procedure will be preserved.

In this example, a stored procedure is developed to report the total outstanding balance and number of outstanding orders for a particular customer. The stored procedure is used in a joint query with another data source.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/stored-procedure/GetOrderInfo.qpr 

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result. Results include:
• Sum: 3000
• Totalorders: 2

If You Want to Create a Query That Uses Stored Procedures

You can find a detailed example describing how to create a stored procedure in Defining Stored Procedures to Liquid Data in Building Queries and Data Views.

References

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/getorderinfo.xsd 

You can find the query statement at:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/stored-procedure/getorderinfo.xq

Custom Functions (DB-UDF) Sample Query

This section includes the following topics related to the DB-UDF sample query:

• What this Query Demonstrates
• How to Run the Queries
• If You Want to Recreate the Custom Functions and the Queries ...

What this Query Demonstrates

This query demonstrates how to create a custom function (also known as a user-defined function). Once the custom functions are defined you can use them in XQuery statements as you would any built-in Liquid Data function.

For example, suppose you need to access legacy data via a custom interface such as a session bean, entity bean, stored procedure and so on. Assume the custom interface is the only way to get data that is needed for another query. In this example, the custom interface is exposed by a session bean which implements two functions: getCustomerOrder( ) and getCustomer( ).

By exposing the interface methods as custom functions, the need to expose the data source and hence all the data in it, to the Liquid Data engine is eliminated.

A custom function only delivers a subset of data from the data source (in this case, Customer Orders and Customers) without exposing other tables and content.

How to Run the Queries

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open either of the following project files:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/sampleProjects/dvbProjects/CustUdf.qpr

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/sampleProjects/dvbProjects/CustOrdUdf.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result.

For the CustUdf query results include:

• First name: JOHN_B_1
• Last name: KAY_1

For the CustOrdUdf query results include:

• Two orders, one for 1000 and one for 1500.

If You Want to Recreate the Custom Functions and the Queries

You can use existing sample data sources when recreating this query.

Create a CFLD file

A CFLD file is where you define your function along with a schema associated with the function's return type. This sample uses the cfld file located at:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFunction.cfld 

The cfld is copied to the Repository under custom_functions folder.

Listing 3-1

<types> 
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"> 

<!-- The schema describing the CustomerOrder object returned --> 

<xs:element name="CustomerOrder"> 
<xs:complexType> 
<xs:sequence> 
<xs:element ref="ORDER_DATE"/> 
<xs:element ref="ORDER_ID"/> 
<xs:element ref="CUSTOMER_ID"/> 
<xs:element ref="SHIP_METHOD"/> 
<xs:element ref="TOTAL_ORDER_AMOUNT"/> 
</xs:sequence> 
</xs:complexType> 
</xs:element> 
<xs:element name="ORDER_DATE" type="xs:date"/> 
<xs:element name="ORDER_ID" type="xs:string"/> 
<xs:element name="SHIP_METHOD" type="xs:string"/> 
<xs:element name="TOTAL_ORDER_AMOUNT" type="xs:decimal"/> 
<xs:element name="CUSTOMER_ID" type="xs:string"/> 
<xs:element name="CustomerOrders"> 
<xs:complexType> 
<xs:sequence> 
<xs:element ref="CustomerOrder" minOccurs="0" maxOccurs="unbounded"/> 
</xs:sequence> 
</xs:complexType> 
</xs:element> 
</xs:schema> 
</types> 

Note that in the CFLD file shown above under TYPE we have defined a customerOrder Element consisting of ORDER_DATE,ORDER_ID,SHIP_METHOD,TOTAL_ORDER_AMOUNT,CUSTOMER_ID

Next create a function.

Listing 3-2

<functions> 
<!-- name is the function name, 
return_type : is the custom method return type 
class: fully qualified classname 
method: the method in the class to map to 
argument: list of argument with type. This is are the input(s) to the method with asscociated labels 
--> 
<!-- The function is mapping to getCustomerOrder in examples.ldi.userDefinedFunc.UserFunctionMapping.class 
It takes to input (url and customer_id), and returns a Element object of type CustomerOrder 
as defined above 
--> 
<function name="getCustomerOrder" return_type="CustomerOrders" class="examples.ldi.userDefinedFunc.UserDefinedFuncMapping" method="getCustomerOrder"> 
<argument type="xs:string" label="url"/> 
<argument type="xs:string" label="customerID"/> 
<presentation group="Sample custom functions" /> 

<description>Get Customer Order gets a list of customer order for a given customer id </description> 

</function> 

Create a Mapping class

Finally, create a mapping class that maps the functions defined in the cfld with the actual implementation.

The mapping class used in this sample is located at:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFuncMapping.java 

public static Element getCustomerOrder(String url, String customerID) 

The method getCustomerOrder( ) matches with the function description specified in the CFLD. Note that its return type is an Element (XML). But this element is described in the CFLD file as a type of customerOrders. Hence the function mapping is responsible for generating an element of type customerOrder. In our sample the mapping function does a JNDI lookup to call getCustomerOrder( ) in the session bean.

Build the getCustomer( ) Query in the Data View Builder

Create a new project.
Click on the Toolbox tab.
Move the following custom function (located in the Sample custom function folder) into the work area:
• getCustomer( )
Set the target schema to custUdf.xsd
Map the following elements from the getCustomer( ) custom function to the target schema:

Source: [getCustomer]/Customers/	Target: [custUdf.xsd]/RESULT/
CUSTOMER/FIRST_NAME	Customer/FIRST_NAME
CUSTOMER/LAST_NAME	Customer/LAST_NAME
CUSTOMER/CUSTOMER_ID	Customer/CUSTOMER_ID

Click on the Constants button. Enter the URL t3://localhost:7001 into the string field.
Map the URL address to the user-defined function url:

Source: Constant	Target:
/t3://localhost:7001	[UDF:getCustomer]/url

Change the string constant value to CUSTOMER_1.
Map the CUSTOMER_1 constant to the user-defined function Customer_ID element

Source: Constant	Target:
CUSTOMER_1	[UDF:getCustomer]/CustomerID

Click the Run Query button and view the result. Results include:
• First name: JOHN_B_1
• Last name: KAY_1
• CustomerID: CUSTOMER_1

Build the getCustomerOrder( ) Query in the Data View Builder

Create a new project.
Click on the Toolbox tab.
Move an instance of the following custom function into the work area:
• getCustomerOrder( )
Set the target schema to custOrdUdf.xsd
Map the following elements from the getCustomer( ) custom function to the target schema:

Source: [UDF:getCustomerOrder]/CustomerOrders/	Target: [custOrdUdf.xsd]/RESULT/
CustomerOrder/ORDER_DATE	CustomerOrder/ORDER_DATE
CustomerOrder/ORDER_ID	CustomerOrder/ORDER_ID
CustomerOrder/CUSTOMER_ID	CustomerOrder/CUSTOMER_ID
CustomerOrder/SHIP_METHOD	CustomerOrder/SHIP_METHOD
CustomerOrder/TOTAL_ORDER_AMOUNT	CustomerOrder/TOTAL_ORDER_AMOUNT

Click on the Constants button in the left pane, enter the URL t3://localhost:7001 in the string field.
Map the URL to the appropriate input parameter of the user-defined function:

Source: Constant	Target:
/t3://localhost:7001	[UDF:getCustomerOrder]/url

Change the string constant value to CUSTOMER_1.
Map the string constant to the other input parameter of the user-defined function:

Source: Constant	Target:
CUSTOMER_1	[UDF:getCustomerOrder]/CustomerID

Enter Test mode and run the query. Results include:
• Two orders, one for 1000 and one for 1500.

If You Want to Build the Sample Source Code

If you want to build a custom function, you can build the sample source code as follows.

Run setLDExampleEnv.cmd or setLDExampleEnv.sh located at:

<WL_HOME>/samples/domains/liquiddata/

This will set up your environment variables needed to run the query.

Add ant to your path.
Compile the source code via build.xml using ant:

cd <WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/build 
ant 

This will compile the script and generate the .ear and other .jar files for you in the output directory.

Find the compiled code at:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/build/output. 

Copy the ldsample_udf.ear to the applications directory of your development domain:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/build/output/applications/ldsample_udf.ear 

to:

<WL_HOME>/samples/domains/liquiddata/applications 

Copy ldsample_udf_rep.jar and ldsample_clientAPI.jar to the domain's repository under the custom_lib folder.
Copy:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/build/output/mapping_classes/*.jar

to

<WL_HOME>/samples/domains/liquiddata/ldrepository/custom_lib 

Copy UserDefinedFuncMapping.cfld to the repository under the custom_functions folder.
Copy:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFuncMapping.cfld 

to:

<WL_HOME>/samples/domains/liquiddata/ldrepository/custom_functions 

On the ldConsole server, Liquid Data node refresh or create a entry for functionResourcelib giving a name for the function group along with the name of the cfld file.

If you used Data View Builder, you will notice your custom functions have been added.

Reference

You can find the target schema that retrieves customers at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/custUdf.xsd

You can find the target schema that retrieves customer orders at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/custorderUdf.xsd 

You can find the cfld file at:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-udf/src/examples/ldi/userDefinedFunc/UserDefinedFunction.cfld

DB-Web Service Sample Query

This document includes the following topics related to the DB-Web Service sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This query demonstrates how to use Liquid Data to access separate Enterprise Information Systems (EIS) using a single query.

Business Scenario

A wireless service provider uses a relational database management system to manage its product information. The sales price of these product is computed using a Web Service. To provide an integrated product and price information, we use liquidData engine to seamlessly access product and price across EISs.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/<LD_HOME>/liquiddata/buildquery/db-webservice/WSPricer.qpr 

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result. Results include:
• Five products (E110, E900, NOK9250, S6225, SS8), each with a sales price of $100.

If You Want to Recreate the Query

You can use existing sample data sources when recreating this query.

Build the Query in the Data View Builder

Create a new project.
Move the following data sources into the work area:

Relational Databases:

• PB-WL (wireless orders RDBMS)

Web services:

• Pricer:getSalesPrice( )
Set the target schema to pricer.xsd
Map the product price element from the Wireless product name to the input element of the getSalesPrice( ) function:

Source: [PB-WL]/db/	Target: Pricer:getSalesPrice/
PRODUCTS/PRODUCT_NAME	string

Map the product name from the Wireless order RDBMS data source (PB-WL) to the target schema:

Source: [PB-WL]/	Target: [pricer.xsd]/PRODUCTPRICE/
PRODUCTS/PRODUCT_NAME	PRODUCT/NAME

Map the product price from the Pricer:getSalesPrice( ) function to the target schema:

Source: Pricer:getSalesPrice/	Target: [pricer.xsd]/PRODUCTPRICE/
result	PRODUCT/SALESPRICE

Enter Test mode and run your query. Results include:
• Five products (E110, E900, NOK9250, S6225, SS8), each with a sales price of $100.

References

You can find the wireless data base schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql 

You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/pricer.xsd

You can access the WSDL File at:

http://localhost:7001/wspricer/Pricer?WSDL 

You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/SalesPrice.xq

SQL_Call Sample Query

This document includes the following topics related to the SQL_Call sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This query demonstrates how to use Liquid Data to construct a query that integrates data from a user-supplied SQL statement.

Business Scenario

For example, if there is a requirement to execute complicated or database-dependent SQL, the SQL Call mechanism can be used as a Liquid Data data source. (Note: the SQL Call mechanism should be used sparingly as it involves manual configuration and precludes certain optimizations that Liquid Data might otherwise provide.)

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/samples/liquiddata/buildQuery/sql_call/GetOrderSummary.qpr 

Click the Test tab. (This shows the generated query statement.)
Enter CUSTOMER_% for the parameter CUSTOMER_ID_PATTERN
Click the Run Query button and view the result. Results include:
• California: 2 order count, average 1000
• Washington: 6 order count, average 2333.3333...

If You Want to Recreate the Query ...

You can use existing sample data sources when recreating this query.

Create a new project.
Move the following data sources into the work area:

SQL Calls:

• PB-WL/GetOrderSummarySQL
From the File menu select Set selected schema as target schema.

The elements in the PB-WL:GetOrderSummarySQL schema will be replicated in the target schema.

Click on the Toolbox tab and select Query Parameter.
Enter CUSTOMER_ID_PATTERN in the name field.
Select xs:string as the type and click Create.
Map the constant to the customer identifier:

Source: Constant	Target: [PB-WL:/GetOrderSummarySQL]/
CUSTOMER_ID_PATTERN	CUSTOMER_ID_PATTERN

Map the elements from the GetOrderSummarySQLCalls web service providing wireless order data source to the target schema:

Source: [PB-WL:GetOrderSummarySQL]/OrderSummarys/resultSetOrderSummary/	Target: [PB-WL:/GetOrderSummarySQL2]/OrderSummarys/resultSetOrderSummary/
orderSummaryRow/STATE	orderSummaryRow/STATE
orderSummaryRow/ORDER_COUNT	orderSummaryRow/ORDER_COUNT
orderSummaryRow/AVERAGE	orderSummaryRow/AVERAGE
orderSummaryRow/MIN	orderSummaryRow/MIN
orderSummaryRow/MAX	orderSummaryRow/MAX

Enter Test mode.
Enter CUSTOMER_% for the CUSTOMER_ID_PATTERN parameter.
Run the query. Results include:
• California: 2 order count, average 1000
• Washington: 6 order count, average 2333.3333...

SQL Call Description file

The SQL Call Description File (pbsp.xsd) defines a SQL Call named GetOrderSummary as the SQL shown below.

Listing 3-3 SQL Call Description File (pbsp.xsd)

	SELECT
		state,
		count(STATE),
		avg(total_order_amount),
		min(total_order_amount),
		max(total_order_amount) 
	FROM  
		customer,
		customer_order
	WHERE 
		((state = 'CA') OR
		 (state = 'OR') OR
		 (state = 'WA')) AND 
		customer.customer_id like ? AND
		customer.customer_id = customer_order.customer_id
	GROUP BY state
	

References

• You can find the Wireless data base schema file at:

<WL_HOME>/samples/domains/liquiddata/scripts/ddl/WIRELESSP.sql

• You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/getordersummary.xsd

• You can find the SQL Call Definition File at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/sql_calls/pbsp.xsd

• You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/getordersummary.xq

CSV-XML Sample Query

This document includes the following topics related to the CSV-XML sample query:

• What This Query Demonstrates
• How to Run the Query
• If You Want to Recreate the Query ...
• References

What This Query Demonstrates

This sample customer information query demonstrates how to use Liquid Data to create an integrated view that shows the connection of two different data sources, a CSV (i.e. delimited) file which originated in a spreadsheet and an XML data source.

Creating an integrated view provides the ability to seamlessly access different sources using a single query.

Business Scenario

An XML file contains a larger set of information about customers (such as name, contact info, and so on) while a CSV file contains a smaller set of customers information such as age. The two sources share the same key: customerID. The goal of query is to gather all information about customers that are younger or at age 40.

How to Run the Query

Start the Liquid Data Samples server.
Start the Data View Builder.
In the Data View Builder, open the following project file:

<WL_HOME>/samples/liquiddata/buildQuery/csv-xml/GetCustomerByAge.qpr

Click the Test tab. (This shows the generated query statement.)
Click the Run Query button and view the result. Results include information on six customers all of whom are age 40 or younger.

If You Want to Recreate the Query ...

You can use existing sample data sources when recreating this query.

Create a new project.
Move the following data sources into the work area:

XML:

• XM-BB-C

Delimited file:

• cstest1
Create a join (eq) between the two CUSTOMER_ID source elements by dragging one element over the other
Source: [xm-bb-c]/db/	Target: [cstest1]/CustomersInfo/
CUSTOMER/CUSTOMER_ID	CustomerInfo/customer_ID
:
Click on the XQuery Functions.
Open the Comparison operators folder.
Drag the less than [lt] function to the second line in the Conditions area.
Map the cstest1 CSV data source age element to the left side of the [lt] equation.
Click on the Toolbox tab and select Constants.
Enter 41 in the Number field.
Create a condition requiring that only customers age 40 or younger will be retrieved by the query through the following mappings:

Source: [cstest1]/CustomersInfo/	Target: [lt]/
CustomerInfo/age	[left operand]
Source: [CONSTANT]/	Target: [lt]/
41	[right operand]

Close the Function Editor.
In the XM-BB-C data source schema right-click on the CUSTOMER complex element name and choose Copy.
In the target schema right-click on results; choose Paste and Map.
Also in the target schema right-click on CUSTOMER and choose Expand complex mapping. (This is just an easier way of individually mapping the elements from the source to the target schema.)
From the File menu select Save Target Schema. Navigate to the Repository folder and save the schema file to the name customerInfo2.xsd.
In the cstest1 source schema right-click on the age element and choose Copy.
In the target schema right-click on CUSTOMER and choose Paste. The age element appears in the target schema.
Map the age element from the CSV data source to the same name element in the target schema:

Source: [cstest1]/CustomersInfo/	Target: [customerInfo2]/results/
CustomerInfo/age	CUSTOMER/age

Click on Test mode and run the query. Results include information on six customers all of whom are age 40 or younger.

References

• You can find the CSV file schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/csvtest1.xsd

• You can find the BroadBand XML schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/b-c.xsd

• You can find the target schema at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/schemas/getCustomerInfo.xsd

• You can find the query statement at:

<WL_HOME>/samples/domains/liquiddata/ldrepository/stored_queries/csv_xml.xq

EJB API Sample

This section references the EJB API sample query.

To build the EJBAPI testing classes

Navigate to the directory:

<WL_HOME>/samples/domains/liquiddata/

If you are on a Windows system run:

setLDExamplesEnv.cmd

Or if you are on a UNIX system run:

. setLDExamplesEnv.sh

on unix bsh)

go to:

<WL_HOME>/samples/liquiddata/ejbAPI/build

3. run ant in the same directory. The class will be generated under

<WL_HOME>/samples/liquiddata/ejbAPI/obj 

To run the ejbAPI test classes

Make sure the sample server is started correctly and running in the non-secure mode.

Go to:

<WL_HOME>/samples/liquiddata/

run:

setLDExamplesEnv.cmd (or run setLDExamplesEnv.sh on unix bash)

Go to:

<WL_HOME>/samples/liquiddata/ejbAPI/obj

Enter the following string for:

Windows:

java -cp .;%CLASSPATH% ejbSample.QueryClient t3://localhost:7001

UNIX:

java -cp . :$CLASSPATH ejbSample.QueryClient t3://localhost:7001

You will see the result on the screen.

Enter the following string for:

Windows:

java -cp .;%CLASSPATH% ejbSample.QueryParamClient t3://localhost:7001 orderparam CUSTOMER_1

UNIX:

java -cp .:$CLASSPATH ejbSample.QueryParamClient t3://localhost:7001 orderparam CUSTOMER_1

you will see the result on the screen.

To examine the code

The source code is under the following directory:

<WL_HOME>/samples/liquiddata/ebjAPI/src