Field expressions
You can transform 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, map SourceFieldA and SourceFieldB to TargetFieldC.
Data Accelerator for Azure 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 Accelerator for Azure concatenates the source text fields by default. You can change the default expression.
Data Accelerator for Azure provides a transformation language that includes SQL-like functions to transform source data. Use these functions to write expressions to modify data or test whether data matches the conditions that you specify.
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.
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 for expressions
Use the following rules when you write expressions:
- •You cannot use strings in numeric expressions.
For example, the expression 1 + '1' is not valid because you can only perform addition on numeric datatypes. 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 datatypes 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.
- •Azure data sync 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. Azure data sync 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. Azure data sync tasks evaluate the expression starting with the innermost function.
Comments in 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 Accelerator for Azure ignores 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 Accelerator for Azure ignores 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 Accelerator for Azure does 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
- •SESSSTARTTIME
- •SPOUTPUT
- •SYSDATE
- •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.
Creating a field expression
Create a field expression on the Field Mapping tab when you configure an Azure data sync task.
1. In the Field Mapping tab, click the f/x icon for the target field that you want to configure.
2. Enter the field expression. You can use the Fields and Functions tabs and the operators shown in the Field Expression dialog box to assist you when you build the expression.
3. Click Validate to validate the expression.
4. Click OK.
Validating expressions in field mappings
Data Accelerator for Azure validates for certain errors that can occur in field mapping expressions.
When you validate a field mapping, Data Accelerator for Azure performs the following validations:
- •Verifies that the source and target fields in the task exist in the source or target.
- •Verifies that all column datatypes 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.
- •Verifies that the datatype of a field in an expression matches the datatype expected by the containing function.
Data Accelerator for Azure does not perform the following validations:
- •Check for incompatible datatypes between source and target fields.
- •Check for case-sensitivity discrepancies for field names.
If you map a string source field to a number target field, the validation succeeds. Data Accelerator for Azure tries to convert the string to a number using the atoi (ASCII to Integer) C function.
