Tasks > Data integration tasks > Field expressions

Field expressions

For some task types, you can configure the field mapping. The field mapping defines how source fields are mapped to target fields. You can specify an expression for each field mapping.

You can map multiple source fields to the same target field. For example, you can map SourceFieldA and SourceFieldB to TargetFieldC.

Data Integration might suggest operations when you map multiple source fields to a single target field. For example, if you map multiple text fields to a target text field, Data Integration concatenates the source text fields by default. You can change the default expression.

Data Integration provides a transformation language that includes SQL-like functions to transform source data. Use these functions to write expressions, which modify data or test whether data matches the conditions that you specify.

For more information about functions and the Data Integration transformation language, see Function Reference.

Creating a field expression

Create a field expression in a task wizard.

1On the Field Mappings or Input Parameterspage, select the target field for which you want to add an expression.

2Perform one of the following actions based on your task type:

- Click Add or Edit Expression.
- Click Actions > Edit Expression, or click the field name.

By default, the Field Expression dialog box shows the source field as the expression, which indicates that the target contains the same value as the source.

3Enter the new field expression.

To include source fields and system variables in the expression, you can select them from the Source Fields and System Variables tabs to insert them into the expression or you can add them to the expression manually.

4Click Validate Mapping to validate the field mappings.

5Click Save.

Validating expressions in field mappings

Use the following rules and guidelines when you validate an expression in a field mapping:

• When you validate mappings, Data Integration performs the following validations:

- Verifies that the source and target fields in the task exist in the source or target. If the field does not exist, an error appears.
- Verifies that all column data types are string and all field expressions contain string operations when the source and target are flat files.
- Verifies that the correct parameters are used for each function and that the function is valid.

•The expression validator does not perform case-sensitive checks on field names.
• The expression validator verifies that the data type of a field in an expression matches the data type expected by the containing function. However, the expression validator does not check for incompatible data types between the following sets of objects:

- Source and target fields of tasks.
- Source field in a lookup condition and the lookup field.
- Output of an expression or lookup and the target field.

The expression or lookup with these incompatible data types may validate successfully, but, at runtime, the task fails and an error appears.

•If you map a string source field to a number target field, the validation succeeds. Data Integration tries to convert the string to a number using the atoi (ASCII to Integer) C function.
•The expression validator does not validate lookups.

Transformation language components for expressions

The transformation language includes the following components to create simple or complex expressions:

•Fields. Use the name of a source field to refer to the value of the field.
•Literals. Use numeric or string literals to refer to specific values.
•Functions. Use these SQL-like functions to change data in a task.
•Operators. Use transformation operators to create expressions to perform mathematical computations, combine data, or compare data.
•Constants. Use the predefined constants to reference values that remain constant, such as TRUE.

In mappings in SQL ELT mode, you use your cloud data warehouse's native expression components to create expressions. You don't use the Informatica transformation language to create expressions.

Expression syntax

You can create a simple expression that only contains a field, such as ORDERS, or a numeric literal, such as 10. You can also write complex expressions that include functions nested within functions, or combine different fields using the transformation language operators.

Note: Although the transformation language is based on standard SQL, there are differences between the two languages.

String and numeric literals

You can include numeric or string literals.

Enclose string literals within single quotation marks. For example:

'Alice Davis'

String literals are case sensitive and can contain any character except a single quotation mark. For example, the following string is not allowed:

'Joan's car'

To return a string containing a single quotation mark, use the CHR function:

'Joan' || CHR(39) || 's car'

Do not use single quotation marks with numeric literals. Just enter the number you want to include. For example:

.05

$$Sales_Tax

Rules and guidelines for expressions

Use the following rules and guidelines when you write expressions:

•For each source field, you can perform a lookup or create an expression. You cannot do both.
• You cannot use strings in numeric expressions.

For example, the expression 1 + '1' is not valid because you can only perform addition on numeric data types. You cannot add an integer and a string.

• You cannot use strings as numeric parameters.

For example, the expression SUBSTR(TEXT_VAL, '1', 10) is not valid because the SUBSTR function requires an integer value, not a string, as the start position.

• You cannot mix data types when using comparison operators.

For example, the expression 123.4 = '123.4' is not valid because it compares a decimal value with a string.

•You can pass a value from a field, literal string or number, or the results of another expression.
•Separate each argument in a function with a comma.
•Except for literals, the transformation language is not case sensitive.
•The colon (:), comma (,), and period (.) have special meaning and should be used only to specify syntax.
•Data integration tasks treat a dash (-) as a minus operator.
•If you pass a literal value to a function, enclose literal strings within single quotation marks. Do not use quotation marks for literal numbers. Data integration tasks treat any string value enclosed in single quotation marks as a character string.
•Do not use quotation marks to designate fields.
•You can nest multiple functions within an expression. Data integration tasks evaluate the expression starting with the innermost function.
• When you use a parameter in an expression, use the appropriate function to convert the value to the necessary data type. For example, you might use the following expression to define a quarterly bonus for employees:

IIF((EMP_SALES < TO_INTEGER($$SalesQuota), 200, 0)

Adding comments to expressions

You can use the following comment specifiers to insert comments in expressions:

•Two dashes:

-- These are comments

•Two forward slashes:

// These are comments

Data integration tasks ignore all text on a line preceded by comment specifiers. For example, to concatenate two strings, enter the following expression with comments in the middle of the expression:

-- This expression concatenates first and last names for customers:
FIRST_NAME -- First names from the CUST table
|| // Concat symbol
LAST_NAME // Last names from the CUST table
// Joe Smith Aug 18 1998

Data integration tasks ignore the comments and evaluates the expression as follows:

FIRST_NAME || LAST_NAME

You cannot continue a comment to a new line:

-- This expression concatenates first and last names for customers:
FIRST_NAME -- First names from the CUST table
|| // Concat symbol
LAST_NAME // Last names from the CUST table
Joe Smith Aug 18 1998

In this case, data integration tasks do not validate the expression because the last line is not a valid expression.

Reserved words

Some keywords, such as constants, operators, and system variables, are reserved for specific functions. These include:

•:EXT
•:INFA
•:LKP
•:MCR
•:SD
•:SEQ
•:SP
•:TD
•AND
•DD_DELETE
•DD_INSERT
•DD_REJECT
•DD_UPDATE
•FALSE
•NOT
•NULL
•OR
•PROC_RESULT
•SPOUTPUT
•TRUE
•WORKFLOWSTARTTIME

The following words are reserved for Informatica Intelligent Cloud Services:

•ABORTED
•DISABLED
•FAILED
•NOTSTARTED
•STARTED
•STOPPED
•SUCCEEDED

Note: You cannot use a reserved word to name a field. Reserved words have predefined meanings in expressions.