Transformations can be on a single element or column, or on multiple elements or columns. For example, in a single-element transformation, you might have a column at the source that contains salary in dollars. A matching column on the target side contains a column that is similar but its values are in thousands of dollars. You transform the type of the source column to the type of the target column.
A multiple-element transformation is a many-to-one relationship. For example, you might have a first name element and a last name element at the source side. At the target side is a single name element. The transformation concatenates the first name and last name fields in the source to a single name field in the target. The columns that participate in the transformation must be used as a source in the mapping.
One-to-many correspondences are specified as multiple one-to-one correspondences, each with their own transformation function as necessary.
A database function is a relationship between a set of input data values and a set of result values. For example, the TIMESTAMP function can be passed input data values of type DATE and TIME, and the result is a TIMESTAMP. Functions can be either built-in or user-defined (from the target side of the mapping). Function expressions can be used for data transformations, or in filter or join conditions, or to capture common expression patterns.
Built-in functions are provided with the database manager. They return a single result value. Such functions include column functions (for example, AVG), operator functions (for example, +), and casting functions (for example, DECIMAL).
The mapping editor allows all scalar functions and all aggregation functions in the expression builder. The functions must be compatible with the deployment environment. Here are some of the functions provided by the expression builder:
DAYOFYEAR('1988-12-25')
Replace all occurrence of the letter 'N' in the word 'DINING' with 'VID'. REPLACE ('DINING', 'N', 'VID')
CHAR(Floating_point_column) = Character string
MAX(BONUS + 1000)
ABS(SAMP.EMPLOYEE.EMPNO)
Arithmetic operators can be applied to signed numeric types and datetime types. For example, USER+2 is not valid. The prefix operator + (unary plus) does not change its operand. The prefix operator - (unary minus) reverses the sign of a nonzero operand; and if the data type of A is a small integer, then the data type of -A is a large integer. The first character of the token following a prefix operator must not be a plus or minus sign. The infix operators +, -, *, and / specify addition, subtraction, multiplication, and division, respectively. The value of the second operand of division must not be zero.
A predicate specifies a condition that is true, false, or unknown about a given row or group.
The mapping editor supports the following operators: *, /, +, -, \, <,>, =, %. The mapping editor supports the following keywords: BETWEEN, NOT, LIKE, NULL, IN, EXISTS.
SALARY < 20000 value1 BETWEEN value2 AND value3 DEPTNO IN ('D01', 'B01', 'C01')