Skip navigation.
Downloads | Product Documentation | Support

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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. 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

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

Relational Databases:

- PB-BB (BroadBand orders RDBMS)
- PB-WL (Wireless orders RDBMS)

3. Set the target schema to `dta.xsd`
4. Click on the Toolbox tab.
5. Select Query Parameters.
6. In the name field enter `custID`.
7. Assign a type of xs:string. Then click Create.
8. 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

9. 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

1. Click XQuery Functions in the Toolbox.
2. Move the XQuery string function xf:lower-case into the work area.
3. 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

4. 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

5. Close xf:lower-case.

#### Converting a String to Upper Case

1. Move the XQuery string function xf:upper-case into the work area.
2. 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

3. 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

4. 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.

1. Move two instances of the XQuery string concatenate function into the work area. They will be automatically labeled xf:concat and xf:concat2.
2. 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).
3. 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

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

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

5. 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

1. Map the second concatenation function to the target schema:

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

2. Close both concatenation functions.

#### Determining Average Sale Price

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

1. Move two instances of the XQuery aggregate sum function into the work area. They will be automatically labeled xf:sum and xf:sum2.
2. 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)

3. Move an instance of the XQuery numeric plus (+) function into the work area.
4. 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

5. Move an instance of the XQuery numeric division (DIV) function into the work area.
6. 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

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

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

1. Move two instances of the XQuery aggregate xf:count function into the work area. They will be automatically labeled xf:count and xf:count2.
2. 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)

3. Move an instance of the XQuery numeric plus (+) function into the work area.
4. 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

5. 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

6. Enter Test mode.
7. Supply `CUSTOMER_1` for the custID parameter.
8. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

1. Create a new project.
2. Move the following Relational Database data source schemas into the work area:

- PB-WL (Wireless)
- PB-CR (Relationship Management)

3. Set `promotionData.xsd` from the repository schema directory as the target schema and expand the schema.
4. 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

5. 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

6. In the Toolbox, click on the Constants button, enter `CUSTOMER_1` in the string field.
7. 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

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

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

9. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

6. 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

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

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

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

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

1. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

1. Create a new project.
2. 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

3. Set the target schema to `viewtarget.xsd`
4. 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

5. 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

6. 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

7. 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

8. In the Toolbox choose the greater than function (xf:gt) from Comparison Operators.
9. 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.
10. Click on the Constants button in the left pane, enter `1000` in the number field. Then click Ok.
11. 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

12. Close the GT function whose results you just mapped.
13. Using the second GT function repeat Step 11 using the Wireless (PB-WL) CUSTOMER_ORDER/TOTAL_ORDER_AMOUNT field and the 1000 constant.
14. 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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. 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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

1. Create a new project.
2. 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)

3. Set the target schema to `threeds.xsd`
4. 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

5. 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

6. 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

7. Click on the Toolbox tab.
8. Click Constants. Enter `CUSTOMER_1` in the String field.
9. 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

10. 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

11. Create the following greater than (GT) tests which will filter out orders less than $1,000:
12. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

1. Create a new project.
2. 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)

3. Set the target schema to `customerOrders.xsd`
4. Click the Toolbox tab.
5. Click the Constants button, enter `JOHN_1` in the string field.
6. 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

   :
7. Enter `KAY_1` in the constant string field.
8. 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

9. 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

10. 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

11. 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

12. In the Constants string field enter `OPEN`.
13. 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

    :
14. 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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. 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

1. Start the Liquid Data Samples server.
2. Start the Data View Builder.
3. 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

1. Click the Test tab. (This shows the generated query statement.)
2. 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

1. Create a new project.
2. Click on the Toolbox tab.
3. Move the following custom function (located in the Sample custom function folder) into the work area:

- getCustomer( )

4. Set the target schema to `custUdf.xsd`
5. 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

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

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

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

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

10. 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

1. Create a new project.
2. Click on the Toolbox tab.
3. Move an instance of the following custom function into the work area:

- getCustomerOrder( )

4. Set the target schema to `custOrdUdf.xsd`
5. 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

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

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

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

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

10. 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.

1. 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.

1. Add `ant` to your path.
2. 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.

3. Find the compiled code at:

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

4. 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

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

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

to

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

1. Copy `UserDefinedFuncMapping.cfld` to the repository under the `custom_functions` folder.
2. Copy:

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

to:

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

1. 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

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

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

4. Click the Test tab. (This shows the generated query statement.)
5. 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

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

Relational Databases:

- PB-WL (wireless orders RDBMS)

Web services:

- Pricer:getSalesPrice( )

3. Set the target schema to `pricer.xsd`
4. 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

5. 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

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

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

7. 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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. Enter`CUSTOMER_%` for the parameter `CUSTOMER_ID_PATTERN`
3. 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.

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

SQL Calls:

- PB-WL/GetOrderSummarySQL

3. 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.

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

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

8. 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

9. Enter Test mode.
10. Enter `CUSTOMER_%` for the CUSTOMER_ID_PATTERN parameter.
11. 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

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

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

1. Click the Test tab. (This shows the generated query statement.)
2. 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.

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

XML:

- `XM-BB-C`

Delimited file:

- `cstest1`

3. 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

   :
4. Click on the XQuery Functions.
5. Open the Comparison operators folder.
6. Drag the less than [lt] function to the second line in the Conditions area.
7. Map the cstest1 CSV data source `age` element to the left side of the [lt] equation.
8. Click on the Toolbox tab and select Constants.
9. Enter `41` in the Number field.
10. 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]

11. Close the Function Editor.
12. In the XM-BB-C data source schema right-click on the CUSTOMER complex element name and choose Copy.
13. In the target schema right-click on `results`; choose Paste and Map.
14. 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.)
15. From the File menu select Save Target Schema. Navigate to the Repository folder and save the schema file to the name `customerInfo2.xsd`.
16. In the `cstest1` source schema right-click on the `age` element and choose Copy.
17. In the target schema right-click on CUSTOMER and choose Paste. The `age` element appears in the target schema.
18. 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

19. 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

1. 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)

2. 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.

1. Go to:

>     <WL_HOME>/samples/liquiddata/

run:

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

2. Go to:

>     <WL_HOME>/samples/liquiddata/ejbAPI/obj

3. 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.

4. 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