User Defined Function (Informatica)

What is user defined function(UDF) ?

UDF is nothing like a expression logic , where you can use the same function or logic  multiple times in your mapping.

Why we need UDF ?

Example :

If you have 20 columns in your mapping in different expression where you want to check NULL constraint  for each column like (IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’) .

So we will use the same logic 20 times in our mapping.

Here (IIF( ISNULL(LTRIM(RTRIM(INPUT))),’TRUE’,’FALSE’)  logic is used lot many times in one expression and in downstream also .

To avoid multiple usage occurrence we can create one UDF with the same logic for different Inputs like

How to create UDF ?

Step 1 :   In Right-click the User-Defined Functions folder in a repository folder in the Designer and Right-click a user-defined function in a repository folder in the Designer.(We can also create UDF by Tools menu)

Step 2: In Editor add the inputs that u want do .(if concat we need two inputs ,so add two input in editor) . If you click the Launch Editor u can see all the functions that u r using in Expression Transformation. Click ok and Validate the UDF.

Type :

Public if the function is callable from any expression. Private if the function is only callable from another user-defined function.

How to use UDF?

After creating UDF , we can see the UDF functions in our expression as below.

Conclusion:

So whenever we need to implement the same logic in different times in our mapping or in our project UDF can be used effectively .

My Profile