User Defined Function (UDF’s) is a way Powercenter gives us to create our own personal, reusable, functions. We can use our UDF’s in Expression Transformations in order to reuse expression logic.
For instance, if you want to RTRIM() LTRIM() and UPPER() several ports, so, instead of writing all these 3 functions all the time you can simply create a UDF and call that UDF.
To create go to User-Defined Functions, right click it > new.
This is User-Defined Function screen.
Some key points about UDF’s:
Type is either PUBLIC or PRIVATE:
- 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.
Although you can place a user-defined function inside another user-defined function, a function cannot refer to itself. For example, the UDF RemoveSpaces includes a UDF TrimLeadTrailSpaces. TrimLeadTrailSpaces cannot include RemoveSpaces; otherwise, RemoveSpaces is invalid.
User-Defined Function practice
Here we created a User Defined Function to do pretty much the same job we did with our mapplet, adjust some basic text string.
Notice that our UDF is a public one.
Here’s our code. It check if it’s null, if it is then set ‘THIS IS A NULL ROW’, otherwise UPPER and TRIM the text.
ISNULL( TEXT_INPUT )
, 'THIS IS A NULL ROW'
, UPPER( LTRIM( RTRIM( TEXT_INPUT ) ) )
Here’s our UDF in use within our mapping:
Notice that for calling an UDF function it is used “:UDF.”, check image to see the details.
After running this mapping this is our result: