DB-AppView Sample Query Readme

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

What this Query Demonstrates

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