Table of Contents

Search

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

Data Validation Option User Guide

Data Validation Option User Guide

Data Type, Precision, and Scale of Field Expressions

Data Type, Precision, and Scale of Field Expressions

In the Expression Editor, you add fields as an expression operand or as a parameter to a PowerCenter function. The field data types and the function return types must be compatible with the data type of the expression result. Additionally, the precision and scale of the expression must be adequate to accommodate the expression result. Incompatibilities can result in a truncated result or test errors.
The following image shows the
Expression Editor
dialog box:
The Expression Editor consists of a Functions tab, a Fields tab, a text box in which to edit the expression, and interface elements to specify the data type, precision, and scale of the PowerCenter data type that you select for the expression. Depending on the tab that you click, the properties area displays the description of a selected function or the properties of the selected field.
  1. Fields tab
  2. Functions tab
  3. Expression text box
  4. Field properties
  5. PowerCenter transformation data type for the expression
  6. Precision of the PowerCenter transformation data type
  7. Scale of the PowerCenter transformation data type
The Expression Editor retrieves fields from the table on which you are running a test. The Expression Editor also displays the properties that the data source associated with the fields when PowerCenter retrieved the fields from the source. The field properties include the data type, precision, and scale of the field.
Precision is the maximum number of significant digits for numeric data types, or the maximum number of characters for string data types. Precision includes scale.
Scale is the maximum number of digits after the decimal point for numeric values. Therefore, the value 11.47 has a precision of 4 and a scale of 2. The string
Informatica
has a precision (or length) of 11.
After Data Validation Option processes an expression, it stores the expression result in a field that has a PowerCenter transformation data type. Data Validation Option also applies the precision and scale of the PowerCenter data type to the expression result. Therefore, when you create an expression, you must know the properties of the fields that you add, and you must set the data type of the expression to a compatible PowerCenter data type. You must also set the precision and scale to values associated with the PowerCenter transformation data type.
The following image shows the PowerCenter transformation data types:
Data Validation Option converts all data to a set of PowerCenter transformation data types. The PowerCenter transformation data types are bigint, binary, date/time, decimal, double, integer, nstring, ntext, real, small integer, string, and text.
After you create an expression, you can it.
You can validate the expression and not the type, precision, or scale.