Use a custom program

For details on creating a transformation using a custom program, see this video:

If the logic you need for a table mapping is too complex to do in the user interface for generated transformations, use a custom program. For example:

• To perform data aggregation, case statements, or complex calculations.

• To use 1-to-many or many-to-many column mappings.

• To call an API to set a flag on records that meet specified criteria; see the Oracle Health Sciences Life Sciences Warehouse Application Programming Interface Guide for information on APIs for Oracle LSH and DMW, and see "Sample Program that Calls the API to Set a Flag" on page 6‐9.

Tip:

If you use multiple custom programs with the same name for different target tables in the transformation, the programs must all be in the same location. In other words, they must be the same program. These conflicts can arise if the custom program is part of a template and therefore part of the study created from the template, for example.

If you reference two custom programs with the same name in different locations, installation ends with a warning and the conflicting packages are listed in the installation log file. The validation process, which must be run before installation, marks all mappings with conflicting package names as Invalid in the UI, and gives the full path for each. You must resolve the conflicts and reinstall the transformation before execution can succeed.

You must create custom programs in LSH. See Create custom programs.

1. Select the source and target tables and click the Map icon.
2. In the Type column, select the type of transformation that the custom program actually performs, if possible: Direct, Join, Union, Pivot, or Unpivot.

   Select Custom only if the code does not do one of these. Data lineage tracing does not work if you select Custom.

3. Click the Select Custom Program icon. Search for the program you want. Use the Query By Example fields above the columns. If they are not displayed, click the Query By Example icon.
4. Select a program and click OK.

   Tip:

   You can open the Static Packages tab and use the drop-down to display either all packages, only those that are already selected in the transformation, or all except those already selected.