Database Reference
In-Depth Information
The Expression transformation
Expression transformations are used for row-wise manipulation. For any type of manipula-
tion you wish to perform on an individual record, use an Expression transformation. The
Expression transformation accepts the row-wise data, manipulates it, and passes it to the
target. The transformation receives the data from the input port and sends the data out from
output ports.
Use Expression transformations for any row-wise calculation, such as if you want to con-
catenate the names, get the total salary, and convert it to uppercase. To understand the func-
tionality of the Expression transformation, let's take a scenario.
Phase - Using the Designer Screen Basics
, concatenate
FIRST_NAME
and
LAST_NAME
to get
FULL_NAME
and
TOTAL_SALARY
from
JAN_SALARY
and
FEB_SALARY
of an
individual employee.
We are using Expression transformation in this scenario because the value of
FULL_NAME
can be achieved by concatenating
FIRST_NAME
and
LAST_NAME
of an individual record.
Similarly, we can get
TOTAL_SALARY
using
JAN_SALARY
and
FEB_SALARY
. In other
words, the manipulation required is row-wise.
We are going to learn some basic aspects of transformations, such as ports in transforma-
tions, writing a function, and so on while we implement our first transformation using an
expression.
Perform the following steps to achieve the functionality:
1. Create the source using flat file in
Source Analyzer
and the target in
Target
Designer
. We will be using
EMP_FILE
as the source and
TGT_EMP_FILE
as the
target.
2. Create a new
m_EXP_CONCAT_TOTAL
mapping in Mapping Designer, drag the
source and target from the navigator to the workspace, and create the Expression
transformation with the
EXP_CONCAT_TOTAL
name.
3. Drag-and-drop all the columns from the source qualifier to the Expression trans-
formation. At this point, the mapping will look as shown in the following screen-
shot: