# DB-CPTCO Sample Query Readme

A copy of this read me is included with the DB-CPTCO sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/db-cptco/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 order query demonstrates how to use Liquid Data to create an integrated view that shows the connection of two different Enterprise Information Systems (EIS), database, and Complex Parameter Type (CPT). Creating an integrated view provides the ability to seamlessly access separate EISs using a single query.

For example, 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 Complex Parameter Type (CPT). The xml for CPT consists of customer id along with one or more new orders with price and quantity that the customer will like to place. The objective is to accept or reject orders if the total amount due from all the outstanding orders (i.e. open orders) in broadband database and the total outstanding balance if the new orders are accepted is above or below the threshold. If the orders are above a limit then output Order Rejected else output Order Accepted. 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>/liquiddata/samples/buildQuery/db-cptco/coCPTSample.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. Specify the Query Parameter and CPT stream

a. Specify a value for orderLimit parameter. If Sum of new orders and all the open orders are above this threshold reject the order, else accept the order

b. Specify a location of the CPT sample XML stream for CO-CPTSAMPLE parameter for example <WL_HOME>/ liquiddata/samples/config/ld_samples/ldrepository/xml_files/coCptSample2.xml

6. 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 BroadBand relational database data source as follows:

- JDBC connection pool: MyBroadBandPool (user= broadband, password= broadband)
- JDBC data source: MyBroadBandDS
- Liquid Data "Relational Database" data source: MyBroadBand-LD-DS (Schema=BROADBAND)

2. Configure the myCO-CPTSAMPLE CPT as follows:

- Alias: myCO-CPTSAMPLE
- Schema: coCptSample2.xsd
- Namespace URI: urn:schemas-bea-com:ld-cocpt
- Schema Root Element Name: CustOrder

### Build the Query in the Data View Builder

1. Create a new project.
2. Open the Data Sources you just defined: a relational database called MyBroadBand-LD-DS and an CPT called myCO-CPTSAMPLE
3. Add COCPTSampleTargetSchema.xsd from the repository as the target schema
4. Follow the steps in the Getting Started guide under "Step 5. Construct the Query" to build the Order Query. (The Getting Started guide is located on edocs at http://download.oracle.com/docs/cd/E13190_01/liquiddata/docs11/qkstart/index.html)
5. Intermediate steps

a. To calculate the sum of new orders we cast the Price and Quantity from CO-CPTSAMPLE to xs:decimal and multiplied them. Sum all the new Orders. Then add both sums together. Follow the same sterps for the BroadBand datasource.

b. Compared the above sum with the OrderLimit parameter to decide whether to accept or reject the Order.

6. Specify your query Parameter and CPT xml stream.
7. Run the query. (Click the Test tab and click Run Query button.)

### Reference

- You can view the BroadBand database schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/BROADBANDP.sql
- You can view the CO-CPTSAMPLE XML schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/ coCptSample2.xsd
- You can view the target schema at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/schemas/ COCPTSampleTargetSchema.xsd
- You can view the query statement at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/ coCPTSample2.xq