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 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
- Start the Liquid Data Samples server.
- Start the Data View Builder.
- In the Data View Builder, open the following project
file: <WL_HOME>/liquiddata/samples/buildQuery/parameterized-view/viewSample.qpr
- Click the Test tab. (This shows the generated query statement.)
- Enter parameter value, such as CUSTOMER_2
- 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
- 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
- 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
- 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
- Create a new project.
- 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.
- Add pviewsrc.xsd from the repository as the target schema,
and expand it.
- 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.
- 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.
- Run the query. (Click the Test tab and click Run Query
button.)
- 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)
- Configure data view. Follow instruction in Liquid Data
Administration Guide to configure data view (named pview1).
- Reconnect to Liquid Data server, and select File->Connect,
the newly configured data view should appear.
- 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.
- Create a new project, open target schema ‘pviewSample’ from
repository.
- Open Data Sources pview1, and MyWireless-LD-DS.
The query joins customer information from MyWireless-LD-DS with order
information from pview1.
- Map all element from view:pview1 output to corresponding
elements in the target schema.
- Map customer information from MyWireless-LD-DS/db/CUSTOMER
to corresponding elements in the target schema.
- Create join relationship by dragging CUSTOMER_ID from
MyWireless-LD-DS/db/CUSTOMER to input parameter of view:pview1.
- Create a parameter ‘cust_id’ of ‘string’ type,
and map it to CUSTOMER_ID in MyWireless-LD-DS/db/CUSTOMER.
- 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