Mapping with functions is similar to straight mapping, except that one or more functions manipulate the source data before it is mapped to the target Description.
As with straight mapping, mapping with functions must be performed within the SELECT sub-command.
The following examples illustrate how you can use functions to accomplish some commonly used mapping scenarios.
Example 1
Concatenating Multiple Source Fields into a Single Target Field
Concatenate SOURCE_FIELD1 and SOURCE_FIELD2 and map the result to TARGET_FIELD1 using the STRING function.
PROCESS INTO OUTPUT_DATASTORE
SELECT
TARGET_FIELD1 = STRING(SOURCE_FIELD1, SOURCE_FIELD2)
FROM INPUT_DATASTORE;Example 2
Adding Multiple Numeric Fields and Storing the Result in a Target Field
Add SOURCE_FIELD1 and SOURCE_FIELD2 together and map them to TARGET_FIELD1 using the ADD function. Add SOURCE_FIELD2 and SOURCE_FIELD3 together and map them to TARGET_FIELD2 using the plus sign (+) operator.
PROCESS INTO OUTPUT_DATASTORE
SELECT
{
TARGET_FIELD1 = ADD(SOURCE_FIELD1, SOURCE_FIELD2)
TARGET_FIELD2 = SOURCE_FIELD2 + SOURCE_FIELD3
}
FROM INPUT_DATASTORE;Example 3
Extract a Portion of a Source Field Before Mapping it to a Target Field
Extract the first three characters of SOURCE_FIELD1 and map them to TARGET_FIELD1. Extract the last three (3) characters of SOURCE_FIELD2 map them to TARGET_FIELD2.
PROCESS INTO OUTPUT_DATASTORE
SELECT
{
TARGET_FIELD1 = LEFT(SOURCE_FIELD1, 3)
TARGET_FIELD2 = RIGHT(SOURCE_FIELD2, 3)
}
FROM INPUT_DATASTORE;Example 4
Translate a Source Field Before Mapping it to a Target Field
Convert the binary data in SOURCE_FIELD1 (3 bytes) to its hexadecimal value and map the result to TARGET_FIELD1.
PROCESS INTO OUTPUT_DATASTORE
SELECT
TARGET_FIELD1 = B2X(SOURCE_FIELD1, 3)
FROM INPUT_DATASTORE;