# Data Transformation Sample Query Readme

A copy of this readme is included with the dataTransform sample in your Liquid Data installation at <WL_HOME>/liquiddata/samples/buildQuery/dataTransform/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 the use of functions for:

- Simple data transformations like to_lower_case, to_upper_case and concatenation
- Aggregation

For example, a wireless service provider has Broadband information stored in one database server under the schema "BROADBAND" and wireless information are managed by the same database server under the schema "WIRELESS". Notice that the information does not have to be stored in the same database server since the data sources are configured separately.The customer representative needs to find out the average spending for customers who use both Broadband and Wireless services. The application developer can use Liquid Data to generate the query to get the information.

## 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/dataTransform/data.qpr
4. Click the Test tab. (This shows the generated query statement.)
5. Click the "Run Query" button and view the XML result. If the query requires query parameters, enter values for these before running the query.

## 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 (Schema=WIRELESS)
2. Configure the BroadBand relational database data source as follows:
   - JDBC connection pool: MyBroadBandPool (username=broadband, password=broadband)
   - JDBC data source: MyBBDS
   - Liquid Data "Relational Database" data source: MyBroadBand-LD-DS (Schema=WIRELESS)

### 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 MyBB-LD-DS
3. Add dta.xsd from the repository as the target schema
4. Drag the function lower_case() from the left-hand side functions panel to the spreadsheet mapping area's source side, then drag the BROADBAND->Customer->FIRST_NAME into the same box as described in Map Source to Target, and drag the FIRST_NAME in the target schema into the right-hand side box and having the two aligned.
5. Drag the function upper_case() and the BROADBAND->Customer->LAST_NAME into the source of the mapping area and the LAST_NAME in the target schema into the target area as step 4.
6. Drag the concat() function into the source side twice then drag the BROADBAND->Customer->LAST_NAME into the source, click the "constant" button on the left to expend the constant panel, in the first textfield, type ", " (do not type the quote, only type the comma into the field), this will concatenate the last name with a comma, drag the BROADBAND->Customer->FIRST_NAME in the same source box, drag the FULL_NAME in the target schema into the corresponding target box.
7. Define the equal join condition on CUSTOMER_ID between source MyBB-LD-DS->db->Customer and souce MyBB-LD-DS->db->Customer_Order as described Join the two sources. Drag the function sum() into the worksapce, drag the TOTAL_ORDER_AMOUNT in MyBB-LD-DS->db->Customer_Order into the parameter1 field of the function and drag the parameter1 into the Scope of the corresponding box in the condition spreadsheet area as the scope of the equal join condition.
8. Repeat the equal join of the CUSTOMER_ID, this time, drag the function count() into the workspace, drag the ORDER_ID into the parameter1 field of the count function and using the parameter1 as the equal join condition.
9. Define the equal join condition on CUSTOMER_ID between source MyBB-LD-DS->db->Customer and souce MyWL-LD-DS->db->Customer_Order as described in step 7.
10. Repeat the equal join of the CUSTOMER_ID of the MyMM-LD-DS->db->Customer and MyWL-LD-DS->db->Customer_Order as described in step 8.
11. Click on Mapping tab to get back to Mapping spreadsheet, drag div from the function panel, drag + from the function panel drop into the same source box, drag the output of the function sum in the workspace, drag the output of the second sum into the same source box so that the box will have "((xf.decimal(anySimpleType?)+xf.decimal(anySimpleType?))div", drag another sum into the box, then drag output of two counts into the box, the final output should the same as the sample schema's. Drag the "average" from the target schema into the target box.
12. Notice in the sample, we added a query parameter called "customer_id" to speed up the query, in all equal joins, hints were given, refer to the manual about using the hint. If this query were run against different database server, make sure to use cast, e.g. drag decimal into the box before dragging the parameter in, so that all the calculation will be based on the same data type.
13. Run the query. (Click the Test tab and click Run Query button. If the query requires query parameters, enter values for these before running the query.)

### 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 XML 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/dta.xsd