Expressions used by the mapping editor

The mapping editor can use many types of expressions in the expression builder.

Expression types

Filters
When you create SQL statements, you might want only a subset of the source data. You might want to extract only the rows that meet certain criteria. You can use filtering in the mapping editor to build an SQL WHERE clause to limit the rows that you eventually extract from a table.
Sort conditions
You can define a sort on columns so that the output is in either ascending or descending alphabetical order. This becomes an ORDER BY clause in the generated SQL script.
Join conditions
You must have more than one source table in the mapping before you can join tables.
Transformations
You can exchange data types between the source and the target in the mapping editor. After you accept the mappings that are identified by the discover function, or after you create mappings, you can add column level transformations to individual columns of mappings. The transformation functions modify the source element values before they are applied to the target.

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.

Functions

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:

Date and time
The Date and Time functions return calendar-related values that can include such values as the name of the day, a timestamp, the day of week, the day of the year, the name of the month, an integer that represents the quarter of the year that the date occurs, among others. For example,
DAYOFYEAR('1988-12-25')
String
The string functions are scalar functions that involve string values.
Replace all occurrence of the letter 'N' in the word 'DINING' with 'VID'. 

REPLACE ('DINING', 'N', 'VID')
Cast
The Cast functions change a value with a given data type to a different data type or to the same data type with a different length, precision or scale. For example,
CHAR(Floating_point_column) = Character string
Aggregation
The aggregate functions operate on a column of values to produce a single value. For example,
MAX(BONUS + 1000)
Math
The math functions perform basic mathematical operations. For example,
ABS(SAMP.EMPLOYEE.EMPNO)

Predicates and operators

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') 

Feedback