# Data View Sample Query Readme

A copy of this readme is included with the data view sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/view/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 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 with source descriptions in the Liquid Data server repository will show up as data sources available for use in any Data View Builder client that connects to the server.

For example, consider a scenario in which it's getting close to the end of the quarter. The Accounts Receivable department at your company is in a hurry to collect receivable from the customers. An outstanding order query has been generated by the MIS team in the Sales department to provide a list 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 source and design another query to work on top on this Data View source. This new query will 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>/liquiddata/samples/buildQuery/view/viewSample.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 relational database data source as follows:
   - JDBC connection pool: MyWirelessPool (username=wireless, password=wireless)
   - JDBC data source: MyWirelessDS
   - Liquid Data "Relational Database" data source: MyWireless-LD-DS
2. Configure the BroadBand relational database data source as follows:
   - JDBC connection pool: MyBroadBandPool (username=broadband, password=broadband)
   - JDBC data source: MyBroadBandDS
   - Liquid Data "Relational Database" data source: MyBroadBand-LD-DS
3. Configure the Data View data source as follows:
   - Name: MyOrder-LD-DS
   - Query File: viewsrc.xv (This file is located in the Repository at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/data_views)
   - Schema File: viewsrc.xsd (This file is located in the Repository at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas)

### Build the Query in the Data View Builder

1. Create a new project.
2. Open the Data Sources you just defined: MyWireless-LD-DS, and MyBoardBand-LD-DS
3. Add viewtarget.xsd from the repository as the target schema
4. Map ORDER_ID from SOURCE MyOrder-LD-DS ->results->result->broadband->order element to the TARGET result->broadband->ORDER_ID.
5. Map the FIRST_NAME, LAST_NAME, CUSTOMER_ID elements from SOURCE MyBroadBand-LD-DS->db->CUSTOMER to the TARGET result->broadband corresponding elements.
6. Map the TOTAL_ORDER_AMOUNT from SOURCE MyBroadBand-LD-DS->db->CUSTOMER_ORDER to the TARGET result->broadband->TOTAL_ORDER_AMOUNT.
7. Define the equal join condition on ORDER_ID between Source MyOrder-LD-DS ->results->result->broadband->order and SOURCE MyBroadBand-LD-DS->db->CUSTOMER_ORDER.
8. Define the equal join condition on CUSTOMER_ID between Source MyBroadBand-LD-DS->db->CUSTOMER_ORDER and SOURCE MyBroadBand-LD-DS->db->CUSTOMER
9. Define the greater-than condition on MyBroadBand-LD-DS->db->CUSTOMER_ORDER->TOTAL_ORDER_AMOUNT. Click on the Functions button on the left panel, find the gt function, drag and drop it into the first available row in the condition workspace, drag and drop the MyBroadBand-LD-DS->db->CUSTOMER_ORDER->TOTAL_ORDER_AMOUNT to the same row in the Condition workspace where the gt function is located . Click on the Constants button, enter 1000 in the number field (the field marked with a #), then drag and drop the arrow icon to the same condition row.
10. Map the ORDER_ID from SOURCE MyOrder-LD-DS ->results->result->wireless->order element to the TARGET result->wireless->ORDER_ID.
11. Map the FIRST_NAME, LAST_NAME, CUSTOMER_ID elements from SOURCE MyWireless-LD-DS->db->CUSTOMER to the TARGET result->wireless corresponding elements.
12. Map the TOTAL_ORDER_AMOUNT from SOURCE MyWireless-LD-DS->db->CUSTOMER_ORDER to the TARGET result->wireless->TOTAL_ORDER_AMOUNT.
13. Define the equal join condition on ORDER_ID between Source MyOrder-LD-DS ->results->result->wireless->order and SOURCE MyWireless-LD-DS->db->CUSTOMER_ORDER.
14. Define the equal join condition on CUSTOMER_ID between Source MyWireless-LD-DS->db->CUSTOMER_ORDER and SOURCE MyWireless-LD-DS->db->CUSTOMER
15. Define the greater-than condition on MyWireless-LD-DS->db->CUSTOMER_ORDER->TOTAL_ORDER_AMOUNT. Click on the Functions button on the left panel, find the gt function, drag and drop it into the first available row in the condition workspace, drag and drop the MyWireless-LD-DS->db->CUSTOMER_ORDER->TOTAL_ORDER_AMOUNT to the same row in the Condition workspace where the gt function is located . Click on the Constants button, enter 1000 in the number field (the field marked with a #), then drag and drop the arrow icon to the same condition row
16. 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 BroadBand database schema at <WL_HOME>/liquiddata/samples/config/ld_samples/scripts/ddl/BROADBANDP.sql
- You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/viewtarget.xsd
- You can view the query statement at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/viewSample.xq