# DB-AppView Sample Query Readme

A copy of this readme is included with the DB-DB sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/db-appview-xml/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

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

In this example, the 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, we demonstrate the use of a single XQuery statement 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>/liquiddata/samples/buildQuery/db-appview-xml/threeds.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 Application View data source as follows:

- JDBC connection pool: MyAVWirelessPool (username=wireless, password=wireless)
- JDBC data source: MyAVDS
- Use the Application Integration (AI) console to create the Application View instance: AVDS
- Liquid Data "Application View" data source: LD-AVDS

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

- JDBC connection pool: MyCRMPool (username=crm, password=crm)
- JDBC data source: CRMDS
- Liquid Data "Relational Database" data source: LD-CRMDS (Schema=CRM)

3. Configure the BroadBand XML data source: LD-XMLDS

### Build the Query in the Data View Builder

- Create a new project.
- Open the Data Sources you just defined: LD-XMLDS, LD-AVDS, and LD-CRM
- Add threeds.xsd from the repository as the target schema
- Map output to TARGET schema.
Map the First_Name, Last_name, Customer_ID from SOURCE LD-XMLDS ->db->Customer to corresponding elements in TARGET CustomerInfo->Customer.
Map Total_Order_Amount, Order_ID, Ship_Method from SOURCE LD-AVDS-> getCustomerOrder Function to corresponding elements in TARTGET CustomerInfo->Orders->Order.
Map Promotion_Name from SOURCE LD-CRM->Promotion to TARGET->PromotionPlans->PromotionPlan->PROMOTION_NAME.
Map Plan_Name, From_Date, To_Date, Price from SOURCE LD-CRM->Promotion_Plan to corresponding elements in TARGET->PromotionPlans->PromotionPlan.
- Add condition ‘Customer_ID = ‘CUSTOMER_1’.
Click on the ‘condition ‘ tab at the bottom of the View Builder. This is done by first, find the function ‘eq’ in ‘Functions’ tab, and drag it into condition spreadsheet; second, drag SOURCE->XMLDS->db->Customer->CUSTOMER_ID to spreadshhet, and then click on ‘Constant’ tab, enter the constant ‘CUSTOMER_1’, and drag the constant to condition spreadsheet.
- Create association between Customer and Customer Order.
This is done by dragging Customer_ID in LD-XMLDS->db->Custoemr->Customer_ID to getCustomerOrder function ->Parameter ->Input->CUSTOMER_ID.
- Create association between LD-XMLDS and LD-CRM for promotion information.
Drag STATE in LD-XMLDS to State in LD-CRM ->Promotion->State. Drag Promotion_name in Promotion table to Promotion_name in Promotion_Plan table.
- Run the query. (Click the Test tab and click Run Query button.)

### Reference

- You can view the wireless data base schema file at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/WIRELESSP.sql
- You can view the CRM database schema at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/CRMP.sql
- You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/threeds.xsd