Transformations > Expression transformation > Expression fields

Expression fields

An expression field defines the calculations to perform on an incoming field and acts as the output field for the results. You can use as many expression fields as necessary to perform calculations on incoming fields.

When you configure an expression field, you define the field properties and the calculations that you want to perform. You configure expression fields in the Expression tab.

You can create the following types of expression fields:

•Output
•Variable
•Input Macro
•Output Macro

When you have a long list of types and want to see the type for each expression field, add the Field Type column in the Expression table as shown in the following image.

Right-click in the column heading and choose Field Type.

Output fields

Create an output field to define a calculation that does not require an expression macro.

The following table describes the properties you can define when you create an output field:

Properties	Description
Name	Name of the field.
Type	The data type of the field.
Precision	Total number of digits in a number. For example, the number 123.45 has a precision of 5. The precision must be greater than or equal to 1. You cannot specify a decimal field precision greater than 38.
Scale	Number of digits to the right of the decimal point of a number. For example, the number 123.45 has a scale of 2. Scale must be greater than or equal to 0. The scale of a number must be less than its precision. The scale may cause zero-filling or truncation with rounding. For example, a scale of 5 causes 123.45 to be expressed as "123.45000" while 123.456789 becomes "123.45679".
Default Value	The default value tells the mapping task what to do when the transformation encounters output errors. The default value is not available in mappings in advanced mode. You can enter one of the following values: - ERROR('transformation error'). When a transformation error occurs, the mapping task skips the row and writes the error to the session log or row error log. - A constant or constant expression. The mapping task replaces the error with the constant or constant expression. Nothing is written to the logs. - ABORT. Transformation aborts and the mapping task writes a message to the session log. Default is ERROR('transformation error'). Data Integration validates the output field default value when you save or validate the mapping. If you enter an invalid value, the Mapping Designer marks the mapping as not valid.
Description	Optional. Description of the field. The description can contain up to 4000 characters.

Variable fields

Create a variable field to define calculations that you want to use within the Expression transformation.

The following table describes the properties you can define when you create a variable field:

Property	Description
Name	Name of the field.
Type	The data type of the field.
Precision	Total number of digits in a number. For example, the number 123.45 has a precision of 5. The precision must be greater than or equal to 1. You cannot specify a decimal field precision greater than 38.
Scale	Number of digits to the right of the decimal point of a number. For example, the number 123.45 has a scale of 2. Scale must be greater than or equal to 0. The scale of a number must be less than its precision. The scale may cause zero-filling or truncation with rounding. For example, a scale of 5 causes 123.45 to be expressed as "123.45000" while 123.456789 becomes "123.45679".
Description	Optional. Description of the field. The description can contain up to 4000 characters.

Input macro field

Create an input macro field to represent the input that you want to use in an expression macro. Depending on the type of macro, the input can be fields or constants.

The following table describes the properties you can define when you create an input macro field:

Property	Description
Name	Name of the input macro field.
Type	The data type of the field.
Precision	Total number of digits in a number. For example, the number 123.45 has a precision of 5. The precision must be greater than or equal to 1. You cannot specify a decimal field precision greater than 38.
Scale	Number of digits to the right of the decimal point of a number. For example, the number 123.45 has a scale of 2. Scale must be greater than or equal to 0. The scale of a number must be less than its precision. The scale may cause zero-filling or truncation with rounding. For example, a scale of 5 causes 123.45 to be expressed as "123.45000" while 123.456789 becomes "123.45679". Not editable for all data types.
Description	Optional. Description of the field. The description can contain up to 4000 characters.

Output macro field

Create an output macro field to define the calculations that you want to perform on all incoming fields or constants. The macro input field represents the incoming fields or constants.

The following table describes the properties you can define when you create an output macro field.

Property	Description
Input Macro Field	Name of the input macro field that represents the input of the expression macro.
Output Macro Field	Name of the output macro field in the following format: <prefix>%<input mactiro field name>%<suffix> The output macro field name must contain either a prefix, suffix, or both.
Suffix	Suffix of the output macro field name. Default is _out.
Prefix	Prefix of the output macro field name. Default is none.
Type	The data type of the field.
Precision	Total number of digits in a number. For example, the number 123.45 has a precision of 5. The precision must be greater than or equal to 1. You cannot specify a decimal field precision greater than 38.
Scale	Number of digits to the right of the decimal point of a number. For example, the number 123.45 has a scale of 2. Scale must be greater than or equal to 0. The scale of a number must be less than its precision. The scale may cause zero-filling or truncation with rounding. For example, a scale of 5 causes 123.45 to be expressed as "123.45000" while 123.456789 becomes "123.45679".
Description	Optional. Description of the field. The description can contain up to 4000 characters.