User Defined Functions in Informatica

Reusability is a most important factor in any ETL tool. Its general purpose is to reduce unnecessary coding which ultimately reduces development time and increases supportability. User Defined Functions are one among the reusability feature provided by Informatica PowerCenter. Let’s understand User Defined Functions in detail.

What is User Defined Functions:

User-defined functions are extensions to PowerCenter transformation language. It allows you to create complex expressions using the built-in functions and operators and reuse them in PowerCenter mappings. User-defined functions are of two types.

1. Public: able to call from any user-defined function, transformation expression, link condition expression, or task expression.
2. Private: able to call 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.

How to create User Defined Functions:

You can create a user defined function from Informatica PowerCenter Designer tool. You can invoke the User-Defined Function dialog box from main menu:

Tools > User-Defined Functions > New.

Below given is a user defined function to convert date format into ‘YYYY-MM-DD’ required format.

The user defined function Date_format takes a date/time data type as input and output a string formatted in (‘YYYY-MM-DD’) The expression editor shows the logic used within the user defined function.

We can see all the user defined functions from the repository navigator as shown below.

How to use User Defined Functions:

User defined functions can be used in any expressions just like any other functions. You can choose the available user defined functions from the functions explorer and use in expressions as shown in below image: