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
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
- 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/view/viewSample.qpr
- Click the Test tab. (This shows the generated query statement.)
- 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 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
- Create a new project.
- Open the Data Sources you just defined: MyWireless-LD-DS,
and MyBoardBand-LD-DS
- Add viewtarget.xsd from the repository as the target schema
- Map ORDER_ID from SOURCE MyOrder-LD-DS ->results->result->broadband->order
element to the TARGET result->broadband->ORDER_ID.
- Map the FIRST_NAME, LAST_NAME, CUSTOMER_ID elements from
SOURCE MyBroadBand-LD-DS->db->CUSTOMER to the TARGET result->broadband
corresponding elements.
- Map the TOTAL_ORDER_AMOUNT from SOURCE MyBroadBand-LD-DS->db->CUSTOMER_ORDER
to the TARGET result->broadband->TOTAL_ORDER_AMOUNT.
- 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.
- Define the equal join condition on CUSTOMER_ID between
Source MyBroadBand-LD-DS->db->CUSTOMER_ORDER and SOURCE MyBroadBand-LD-DS->db->CUSTOMER
- 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.
- Map the ORDER_ID from SOURCE MyOrder-LD-DS ->results->result->wireless->order
element to the TARGET result->wireless->ORDER_ID.
- Map the FIRST_NAME, LAST_NAME, CUSTOMER_ID elements from
SOURCE MyWireless-LD-DS->db->CUSTOMER to the TARGET result->wireless
corresponding elements.
- Map the TOTAL_ORDER_AMOUNT from SOURCE MyWireless-LD-DS->db->CUSTOMER_ORDER
to the TARGET result->wireless->TOTAL_ORDER_AMOUNT.
- 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.
- Define the equal join condition on CUSTOMER_ID between
Source MyWireless-LD-DS->db->CUSTOMER_ORDER and SOURCE MyWireless-LD-DS->db->CUSTOMER
- 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
- 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