Table of Contents

Search

  1. Preface
  2. Introduction to Data Validation Option
  3. Repositories
  4. XML Data Source
  5. Tests for XML Data Sources
  6. Connections
  7. Expressions
  8. Table Pairs
  9. Tests for Table Pairs
  10. Single-Table Constraints
  11. Tests for Single-Table Constraints
  12. Examples of Tests from Spreadsheets
  13. SQL Views
  14. Lookup Views
  15. Join Views
  16. Aggregate Views
  17. Business Intelligence and Reporting Tools Reports
  18. Dashboards
  19. DVOCmd Command Line Program
  20. Troubleshooting
  21. Appendix A: Datatype Reference
  22. Appendix B: Reporting Views
  23. Appendix C: Metadata Import Syntax
  24. Appendix D: Jasper Reports
  25. Appendix E: Glossary

Data Validation Option User Guide

Data Validation Option User Guide

SQL View Example

SQL View Example

You want to verify that customers have larger transactions at your store than last quarter. To verify this, you create and run a single-table test based on an SQL view.
To determine if customers have larger transactions, you must verify that the average transaction amount is greater than $100, which was the average transaction amount last quarter. You get this information from transaction tables in an Oracle database.
You create an SQL view based on the transaction tables. Next, you create a single table based on the SQL view. Finally, you create a single-table test to compare the average transaction amount against the $100 constraint value.

Tables and Columns

The following table lists the transaction tables and columns that are included in the SQL view:
Table
Columns
ORDERS
  • ORDER_ID
  • CUSTOMER_ID
  • PRODUCT_ID
  • PRICE_PER_UNIT
  • UNITS_SOLD
CUSTOMERS
  • CUSTOMER_ID
  • LAST_ORDER_DATE
PRODUCTS
  • PRODUCT_ID

Creating the SQL View

Complete the following tasks when you create the SQL view:
  1. Enter Sales_SQLView as the description.
  2. Add ORDERS, CUSTOMERS, and PRODUCTS as the tables in the SQL view.
  3. Select the connection for the Oracle database that contains the transaction tables.
  4. Add the required columns from each table in the SQL view definition.
  5. Enter the following SQL statement in the SQL view definition:
    SELECT AVG(O.PRICE_PER_UNIT), C.CUSTOMER_ID, C.LAST_ORDER_DATE, P.PRODUCT_ID, O.UNITS_SOLD, O.PRICE_PER_UNIT, (O.UNITS_SOLD*O.PRICE_PER_UNIT) AS TRANSACTION_AMOUNT FROM CUSTOMERS C, ORDERS O, PRODUCTS P WHERE (O.CUSTOMER_ID=C.CUSTOMER_ID AND P.PRODUCT_ID=O.PRODUCT_ID AND C.customer_ID IN (SELECT UNIQUE(C.CUSTOMER_ID) FROM CUSTOMERS C)) GROUP BY C.CUSTOMER_ID, C.LAST_ORDER_DATE, P.PRODUCT_ID

Creating the Single Table and the Test

Create a single table based on the SQL view. Next, add an AVG test to the single table based on the TRANSACTION_AMOUNT column in the SQL view. In the test, create a test condition where the average transaction amount exceeds 100.

Running the Test

Run the test. If the test passes, the average transaction amount exceeds $100. Customers made larger transactions than last quarter.