User-Defined Functions

User-defined functions extend the PowerCenter transformation language. we can create and manage user-defined functions with the PowerCenter transformation language in the Designer. We can add them to expressions in the Designer or Workflow Manger to reuse expression logic and build complex expressions. User-defined functions are available to other users in a repository.

Example

If you want to remove leading and trailing spaces from last names. You can create a user-defined function named RemoveSpaces to perform the LTRIM and RTRIM functions. When you configure the user-defined function, you enter the following expression:

LTRIM( RTRIM( name))

After you create the function, you can create the following expression in an Expression transformation to remove leading and trailing spaces from last names:

:UDF.REMOVESPACES(LAST_NAME)

The user-defined function name is prefaced by :UDF. The port name is LAST_NAME. It is the argument of the expression.

Configuring the Function Type

You can place user-defined functions in other user-defined functions. You can also configure a user-defined function to be callable from expressions. Callable means that you can place user-defined functions in an expression.

Select one of the following options when you configure a user-defined function:

• Public. Callable from any user-defined function, transformation expression, link condition expression, or task expression.
• Private. Callable from another user-defined function. Create a private function when you want the function to be part of a more complex function. The simple function may not be usable independently of the complex function.

After you create a public user-defined function, you cannot change the function type to private.

Although you can place a user-defined function in another user-defined function, a function cannot refer to itself. For example, the user-defined function RemoveSpaces includes a user-defined function TrimLeadingandTrailingSpaces. TrimLeadingandTrailingSpaces cannot include RemoveSpaces. Otherwise, RemoveSpaces is invalid.

Step to Create User-Defined Function:

1. Select on ‘User-Defined Function’ in left side navigator.

2. Right click on ‘User-Defined Functions’ à Select ‘New’

3. The Edit User-Defined Function dialog box appears 

Enter a function Name: concatenames

Select a function Type: Public

Add Arguments and Name them, Define Datatype, Precision and Scale

4. Click Launch Editor to create an expression that contains the arguments you defined in step 3.

 

Go to Arguments Tab and create an expression “concat(First_Name, Last_Name)”

Click on ‘Validate’ and ‘OK.

Click on ‘OK’.

5. Now your User-Defined Function is ready to user in Mapplets and/or Mappings.

 

6. Open the Mapping/Mapplet where you want to use ‘UDF’.

You can call User-Defined Function by :UDF.<User-Defined Function Name> and pass the required arguments in sequence as below.

           

Sample syntax: :UDF.CONCATENAMES(First_Name,Last_Name)