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, build a data flow with transformations that use the function.

In the following example, DMLOCS, a location table, is the data target. 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 transformation, STATE, you convert a city abbreviation to its corresponding state code.
  • In the second transformation, PLANT_CITY, you turn the city abbreviation into the complete city name.
  • In the final transformation, PLANT_STATE, you convert a two-character state code into the full state name, using a list of state codes stored in a separate fixed format 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 DMSTDEF should be designated 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 ibi Data Migrator desktop interface, you must first make it available for processing, as illustrated in the example below.

Refer to the sample data flow DMDCODE for the complete example.

Using the DECODE Function in a Data Flow

In the ibi Data Migrator desktop interface:

  1. Right-click an application directory in the navigation pane, select New, and then click Flow.

    The Data Flow tab opens in the right pane, with the SQL object displayed.

    To test the query from ibi Data Migrator, you must first specify the location of the DMDCD file. 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 click 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 click Column Selection.

    The Column Selection dialog 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 checkbox, and click OK to close the Column Selection window.
  8. Right-click in the workspace to the right of the SQL object, select Add Target, and then click 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 click 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 fixed format file, select Delete File. Click X to close.
  12. Right-click the DMLOCS target object and click Target Transformations.

    The Transformations dialog 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 to open the Transformations Calculator.
  17. Enter 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. Enter the following as the transformation expression:
    DECODE STATE (DMDCD)

    Click OK to close the calculator.

    When you have defined the three transformations, the Transformations dialog should appear.

  21. Click the Test transforms button to test the transformations you just built. Click Close.
  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 click Delete. You are 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 then select Run from the dropdown 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, select Operations, and then click Sample Data.