Deploy > Working with Tasks > Field Expressions
  

Field Expressions

You can transform the source data before loading it into the target. When you configure field mappings, you can specify an expression for each field mapping. You can map multiple source fields to the same target field. For example, you map SourceFieldA and SourceFieldB to TargetFieldC.
Informatica Cloud 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, Informatica Cloud concatenates the source text fields by default. You can change the default expression.
Informatica Cloud 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.
To create a field expression, perform the following steps in the task wizard:
    1. In the Field Mappings page, select the target field for which you want to add an expression.
    2. Click Add or Edit Expression.
    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.
    3. Enter the new field expression.
    4. Click Validate Mapping to validate the field mappings.
    5. Click Save.

Validating Expressions in Field Mappings

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

Transformation Language Components for Expressions

The transformation language includes the following components to create simple or complex 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
or
$$Sales_Tax

Rules and Guidelines for Expressions

Use the following rules and guidelines when you write expressions:

Adding Comments to Expressions

You can use the following comment specifiers to insert comments in expressions:
Informatica Cloud 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
Informatica Cloud 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, Informatica Cloud 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 Cloud:
Note: You cannot use a reserved word to name a field. Reserved words have predefined meanings in expressions.