# DB-AppView Sample Query Readme

A copy of this readme is included with the DB-DB sample in your Liquid Data installation at <BEA_HOME>/<WL_HOME>/liquiddata/samples/buildQuery/db-appview/readme.htm. (The default install home for Liquid Data is bea/weblogic700.)

This readme includes the following topics:

- What this Query Demonstrates
- How to Run the Query
- If You Want to Re-create the Query . . .

## 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 ApplicationView and a relational database containing customer information.

For example, due to historical reasons MyTelcom has two separate order management systems: one for wireless service and the other for broadband. The wireless information can only be accessed as an EIS system (via J2EE Connector Architecture and Weblogic Application View), and the broadband 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: <BEA_HOME>/<WL_HOME>/liquiddata/samples/buildQuery/db-appview/03.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. Click the "Run Query" button and view the XML result.

## If You Want to Re-create the Query . . .

### Configure the Data Sources in the Administration Console

1. Configure the Wireless Application View data source as follows:

- JDBC connection pool: pbwpool (username=wireless, password=wireless)
- JDBC data source: pbbbds
- Use Application Integration (AI) console to create an Application View definition: avwlds
- Liquid Data "Application View" data source: AV-WL

2. Configure the BroadBand relational database data source as follows:

- JDBC connection pool: pbbpool (username=broadband, password=broadband)
- JDBC data source: pbbbds
- Liquid Data "Relational Database" data source: PB-BB

### Build the Query in the Data View Builder

1. Create a new project.
2. Open the Data Sources you just defined: “AV-WL” and “PB-BB”
3. Add customerOrders.xsd from the repository as the target schema
4. Define a constant, “JOHN_1” and map it to AV-WL input ->FIRST_NAME, define a constant, “KAY_1” and map it to AV-WL input ->LAST_NAME.
5. Map the First_Name, Last_Name, Customer_id from AV-WL result element to the TARGET Customers->Customer corresponding attributes similar to the Map Source to Target.
6. Drag the Customer_id from AV-WL result element to PB-BB Customer_Order table -> Customer_ID field and give hints “ppright”.
7. Map from SOURCE PB-BB, the Customer_Order->Order_ID, Customer_Order->Order_Date and Customer_Order_Line_Item->Price to TARGET customers->customer->orders->order corresponding attributes
8. Drag from SOURCE PB-BB, the Customer_Order->Order_ID to Customer_Order_Line_Item->Order_ID and give hints “ppright”.
9. Click “Condition” tab, drag “eq” from “Functions” tab to a new row in “Condition”, and then drag from SOURCE PB-BB Customer_Order_Line_Item->Status and a new constant “OPEN” to the same row.
10. Run the query. (Click the Test tab and click Run Query button.)

### Reference

- You can view the BroadBand database schema file at <BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/BROADBANDP.sql
- You can view the target schema at <BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/customerOrders.xsd
- You can view the query statement at <BEA_HOME>/<WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/03.xq