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.

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.


Top of page

Example: Using the DECODE Function in a Data Flow

In the DMC:

  1. 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.

  2. Right-click a server, select My Console and then Edit My profile.
  3. 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.

  4. 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.
  5. Right-click the SQL object and select Column Selection.

    The Column Selection window opens.

  6. Select PLANT from the Available Columns list and click the arrow to move it to the Selected Columns list.
  7. Click the Distinct check box, and click OK to close the Column Selection window.
  8. Right-click in the workspace to the right of SQL object, select Add Target and then Existing.
  9. Select DMLOCS from the ibisamp application directory, and click Select.

    The data target object DMLOCS is added to the workspace.

  10. Right-click the target object DMLOCS, and select Properties.

    The Properties page opens.

  11. 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.
  12. Right-click the DMLOCS target object and select Target Transformations.

    The Transformations window opens.

  13. Click the Insert Transforms button.

    The Transformations Calculator opens.

  14. Enter STATE in the Column field and A2 in the Format field.
  15. In the expression field, type
    DECODE PLANT (BOS MA DAL TX LA CA ORL FL SEA WA STL MO)

    and click OK.

  16. Double-click PLANT_CITY in the Target Columns list. This moves it to the Expressions list. Click Edit Transforms to open the Transformations Calculator.
  17. 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') 
  18. Click OK to close the calculator.
  19. Double-click PLANT_STATE in the target columns list. This moves it to the Expressions list.
  20. 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.

    Transformations window

  21. Click the Test transforms button to test the transformations you just built. Click Close. The following image shows the Test Transformations window.

    Test transforms window

  22. 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.

  23. Click the Process Flow tab.
  24. Right-click the arrow between the Start object and the Data Flow object, and select Delete. You will be inserting the procedure between them.
  25. Drag the procedure, DMSTDEF, from the ibisamp application directory in the navigation pane into the process flow workspace.
  26. 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.
  27. Save the flow as DMXDCODE.
  28. 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.


iWay Software