# Parameterized 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/parameterized-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 these Queries Demonstrates

This sample demonstrates how to construct and use a parameterized view.

In this example, the orders are aggregated from two different data sources: an EIS system (via J2EE Connector Architecture and Weblogic Application View), and a relational data source. The order view is constructed as a parameterized view with CUSTOMER_ID being the input parameter. Once the data view is constructed and configured, it can be used to construct other queries; for example, you can create a query that returns customer information from one data source and all the detail order information from the parameterized view.

## 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/parameterized-view/viewSample.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. Enter parameter value, such as CUSTOMER_2
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 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 Application View data source as follows:

· JDBC connection pool: MyAVWirelessPool (username=wireless, password=wireless)

- JDBC data source: MyAVAVWireless
- Use the Application Integration (AI) console to create the Application View instance: MyAVWirelessDS, and method getCustomerOrder(cust_id)
- Liquid Data "Application View" data source: MyAVWireless-LD-DS

### Build the Parameterized View in the Data View Builder

1. Create a new project.
2. Open the Data Sources you just defined MyAVWireless-LD-DS, and MyBoardBand-LD-DS

Double click on MyBoardBand-LD-DS from ‘Relational databases’ Source tab; double click on MyAVWireless-LD-DS from ‘Application views’ Source tab; and then double click on getCustomerOrder method.

3. Add pviewsrc.xsd from the repository as the target schema, and expand it.

4. Map Order from MyBroadBand-LD-DS to corresponding elements in the target schema

Map ORDER_ID,ORDER_DATE,CUSTOMER_ID,SHIP_METHOD, and TOTAL_ORDER_AMOUNT from SOURCE MyBroadBand-LD-DS ->db->CUSTOMER_ORDER to corresponding elements in TARGET result->BROADBAND_ORDERS->ORDER.

5. Map Order from MyAVWireless-LD-DS:getCustomerOrder to corresponding elements in the target schema

Map ORDER_ID,ORDER_DATE,CUSTOMER_ID,SHIP_METHOD, and TOTAL_ORDER_AMOUNT from SOURCE MyAVWireless-LD-DS:getCustomerOrder ->Output->Rows->Row to corresponding elements in TARGET result->WIRELESS_ORDERS->ORDER.

6. Define parameter for the parameterized view

Create a parameter cust_id of ‘string’ type. Map the parameter to MyBroadBand-LD-DS ->db->CUSTOMER_ORDER->CUSTOMER_ID and CUSTOMER_ID in MyAVWireless-LD-DS:getCustomerOrder->Input->Parameters->Input->CUSTOMER_ID.

7. Run the query. (Click the Test tab and click Run Query button.)

8. Save the query to the Liquid Data Repository. Click ‘File’ menu bar and ‘Save Query’, and choose ‘repository’ directory to save the query (named it pview1)

9. Configure data view. Follow instruction in Liquid Data Administration Guide to configure data view (named pview1).

10. Reconnect to Liquid Data server, and select File->Connect, the newly configured data view should appear.

11. Now you can use the parameterized data view, the following show one example that join a relational data source (for customer info) with the data view.

12. Create a new project, open target schema ‘pviewSample’ from repository.

13. Open Data Sources pview1, and MyWireless-LD-DS. The query joins customer information from MyWireless-LD-DS with order information from pview1.

14. Map all element from view:pview1 output to corresponding elements in the target schema.

15. Map customer information from MyWireless-LD-DS/db/CUSTOMER to corresponding elements in the target schema.

16. Create join relationship by dragging CUSTOMER_ID from MyWireless-LD-DS/db/CUSTOMER to input parameter of view:pview1.

17. Create a parameter ‘cust_id’ of ‘string’ type, and map it to CUSTOMER_ID in MyWireless-LD-DS/db/CUSTOMER.

18. Run the query. (Click the Test tab, provide parameter value, 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/pviewsrc.xsd and pviewSample.xsd
- You can view the query statement at <WL_HOME>/liquiddata/samples/config/ld_samples/ldrepository/stored_queries/pviewsrc.xq, and pviewSample.xq