Using the Decode Function
DECODE functions are used to convert codes into values.
For example, you use DECODE to convert a two-character state code
into the complete state name. DECODE can be explicitly stated or
you can store the decoded values in a separate file.
The primary use of a DECODE function is to convert codes to full
names. To use DECODE in a data flow, you build a data flow with
transformations that use the function.
In the following example, DMLOCS, a location table, is the data
target and we add three transformations to map the source data columns
to the target data columns. These transformations are known only
to this data flow.
- In the first,
the transformation for STATE, we convert a city abbreviation to
its corresponding state code.
- In the second
transformation, PLANT_CITY, we turn the city abbreviation into the complete
city name.
- In the final
transformation, PLANT_STATE, we convert a two-character state code into
the full state name using a list of state codes stored in a separate
flat file identified as DMDCD and named DMDCD.FTM.
The location of this state code file must be specified in the
data flow so that it can be found at execution time. The procedure
DMSTDEF specifies the location of the file and we designate DMSTDEF
as a pre-extract stored procedure. This means that when the data
flow runs, prior to the extraction of data from the source file,
the procedure DMSTDEF runs. The file location is now known and when
the PLANT_STATE transformation is calculated, the state file used
in the decode is found.
In this example, DMSTDEF contains the following code:
FILEDEF DMDCD DISK ibisamp/dmdcd.ftm
-RUN
-TYPE (ICM1) Filedef for State decode file returned &RETCODE
This specifies the location of the file used in the DECODE.
Note: If you want to be able to test the DMSTDEF procedure
from the DMC, you must first make it available for processing, as
illustrated in the example Using the DECODE Function in a Data Flow.
Refer to the sample data flow DMDCODE for the complete example.
Example: Using the DECODE Function in a Data Flow
In
the DMC:
- Right-click
an application directory in the navigation pane, select New and
then Flow. The Data Flow tab opens in the
right pane, with the SQL object displayed.
In order to test the
query from DataMigrator, you must first specify the location of the
DMDCD file. We will do this by adding the procedure DMSTDEF to your
profile to define its location and test it before you begin to construct
the flow.
- Right-click
a server, select My Console and then Edit
My profile.
- When the Edit
User Profile editor opens, enter the line below at the end of the profile:
EX DMSTDEF
Click X to
close the editor, then click Yes to save
the changes.
- Drag the data
source object DMORD from the ibisamp application
directory in the navigation pane into the workspace, to the left
of the SQL object.
- Right-click
the SQL object and select Column Selection.
The
Column Selection window opens.
- Select PLANT from
the Available Columns list and click the arrow to move it to the
Selected Columns list.
- Click the Distinct check
box, and click OK to close the Column Selection
window.
- Right-click
in the workspace to the right of SQL object, select Add Target and
then Existing.
- Select DMLOCS from
the ibisamp application directory, and click Select.
The
data target object DMLOCS is added to the workspace.
- Right-click the target object DMLOCS,
and select Properties.
The Properties
page opens.
- If the flow
may be rerun, you should clear the target using the Prior
to Load Option. Since DMLOCS is a flat file, select Delete File.
Click X to close.
- Right-click
the DMLOCS target object and select Target
Transformations.
The Transformations window opens.
- Click the Insert
Transforms button.
The Transformations Calculator
opens.
- Enter STATE in
the Column field and A2 in the Format field.
- In the expression
field, type
DECODE PLANT (BOS MA DAL TX LA CA ORL FL SEA WA STL MO)
and
click OK.
- Double-click PLANT_CITY in
the Target Columns list. This moves it to the Expressions list.
Click Edit Transforms to open the Transformations
Calculator.
- Double-click
the Transformation Expression field for PLANT_CITY
and type the following as the transformation expression:
DECODE PLANT (BOS 'Boston' DAL 'Dallas' LA 'Los Angeles' ORL 'Orlando'
SEA 'Seattle' STL 'St. Louis')
- Click OK to close the calculator.
- Double-click PLANT_STATE in
the target columns list. This moves it to the Expressions list.
- Double-click
the Transformation Expression field for PLANT_STATE
and type the following as the transformation expression:
DECODE STATE (DMDCD)
Click OK to
close the calculator.
When you have defined the three transformations,
the Transformations window should appear, as shown in the following
image.
- Click the Test
transforms button to test the transformations you just
built. Click Close. The following image shows
the Test Transformations window.
- Click OK to
close the Transformations window.
Note: If you leave EX
DMSTDEF in your profile, then steps 22 to 26 are not needed.
If
another user were going to use this flow, then you would need to
perform the following steps.
- Click the Process
Flow tab.
- Right-click
the arrow between the Start object and the Data Flow object, and
select Delete. You will be inserting the
procedure between them.
- Drag the procedure, DMSTDEF,
from the ibisamp application directory in the navigation pane into
the process flow workspace.
- Drag from the Start object
to the DMSTDEF object to connect them, then
drag from the DMSTDEF object to the Data
Flow object to connect them.
- Save the flow
as DMXDCODE.
- On the Flow
tab, in the Run group, click Run and select Run from
the drop-down menu.
To verify that the target was loaded
properly and that the transformations evaluated properly, click
the Data Flow tab, right-click the DMLOCS data
target and select Operations, and then Sample
Data.