Creating expressions with user-defined functions

When you create an expression, valid user-defined functions appear in the expression editor. If you type in an expression manually, precede each user-defined function with :UDF.

The following image shows a user-defined function selected in the expression editor of an Expression transformation:

When you select a user-defined function, the expression editor shows the function syntax in the following format:

For example:

When you add the function to the expression, the function includes the prefix :UDF, as shown in the following example:

After you add the function to the expression, replace the arguments with field names or in-out parameters. Don't include the table name in the argument. Use only the field name as shown in the following example:

For more information about creating expressions, see Tasks.

When you validate the expression, Data Integration doesn't validate the user-defined function. It only validates the expression.

To parameterize a user-defined function, create a string in-out parameter and enable Is expression variable. To fully parameterize the user-defined function, use the parameter in place of the entire user-defined function. To partially parameterize the user-defined function, use the in-out parameter in place of the user-defined function arguments. At run time, resolve the parameter in the mapping task or parameter file.

For more information about in-out parameters, see Mappings.

Use full parameterization when you want to parameterize the user-defined function in an expression. When you use an in-out parameter in place of a user-defined function, parameter names are case sensitive. When you resolve the parameter, include the :UDF prefix.

For example, you have the in-out parameter $$UDFparameter and you use it to fully parameterize a user-defined function. In the parameter file, you resolve the parameter with the following function:

Use partial parameterization when you want to parameterize the field name in the argument, or when you want to parameterize a nested user-defined function. When you parameterize a nested user-defined function, the parameter in the function argument must be in capital letters. You can nest one user-defined function.

For example, you create the in-out parameter $$UDF and configure it as an expression variable. In the expression, you configure the following function:

In the parameter file, you resolve the parameter with the following function:

You can use both full and partial parameterization within the same function. Use both full and partial parameterization when you want to parameterize the user-defined function and the function arguments.

For example, you have the in-out parameter $$UDF and use it in an expression to fully parameterize a user-defined function. In the parameter file, you resolve the parameter with the following values:

When you want to pass either the CurrentTaskName, CurrentRunId, or SESSSTARTTIME system variable as an argument in a fully parameterized user-defined function, you must pass the system variable through another in-out parameter.

For example, you want use CurrentTaskName in a parameterized user-defined function. You use the in-out parameter $$UDF to fully parameterize the function. In the parameter file, you resolve the parameter with the following values: