Expression editor

Use the expression editor to configure the expression field. The expression can contain constants, variables, built-in functions, and user-defined functions. You can also create a complex expression by nesting functions within functions.

To configure an expression, enter it in the expression editor.

The following image shows the expression editor and the actions you can perform:

You can add source fields, functions, and variables to the expression by clicking Add next to the object that you want to use. You can also type in the expression manually.

Alternatively, press the Ctrl + Space keys to see a list of recommended arguments and functions in-line. Data Integration provides recommendations based on the type of function arguments and keystrokes. In-line recommendations are not available for hierarchical source data.

To validate the expression, click Validate. Data Integration validates the expression.

In mappings in SQL ELT mode, the expression editor displays your cloud data warehouse's native functions instead of Informatica transformation language functions. Data Integration doesn't validate the expression. If you enter an invalid expression, the mapping fails at run time.

Transformation language components for expressions

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

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:

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

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

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

Adding comments to expressions

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

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:

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:

The following words are reserved for Informatica Intelligent Cloud Services:

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