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.
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.
In the DMC:
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.
EX DMSTDEF
Click X to close the editor, then click OK to save the changes.
The Column Selection window opens.
The data target object DMLOCS is added to the workspace.
The erties dialog box opens.
The Transformations window opens.
The Transformations Calculator opens.
DECODE PLANT (BOS MA DAL TX LA CA ORL FL SEA WA STL MO)
and click OK.
DECODE PLANT (BOS 'Boston' DAL 'Dallas' LA 'Los Angeles' ORL 'Orlando' SEA 'Seattle' STL 'St. Louis')
as the transformation expression.
DECODE STATE (DMDCD)
as the transformation expression.
When you have defined the three transformations, the Transformations window should appear as shown in following image.
Note: If you leave EX DMSTDEF in your profile, then steps 23 to 26 are not needed.
If another user were going to use this flow, then you would need to perform the following steps.
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.
iWay Software |