A function is universally meant to wrap your code and help you to reuse it whenever you need it. PowerCenter is already rich with its system functions, however it also facilitate developer to develop a user defined functions. Lets see how to develop user defined functions and the various advantages of it.
Why User defined functions are required?
Maintainability is the globally accepted standard of any code, project, application. Writing code in user defined functions over writing them in transformation expression is more maintainable.
Lets see how it is – I am writing over 10 different payment descriptions for a certain credit card payment application like direct debit, cheque clearance, link payments and cash payments etc etc. Now I can think of few –
- Code it in my expression transformation.
- Create a user defined function for the payment status.
- Have a lookup file for these statuses, lookup for the statuses – For this, you might have to. decide the business owner who can review and submit the lookup file.
3. See below fact sheet that compares a traditional transformation expression with user defined function
Two types of functions available –
Public – Can be called from any transformation expression. Task expression or another user defined function.
Private – Can only be called from another UDF. But cannot be called from transformation expression or any task and link conditions.
How to create a User Defined Function (UDF)?
- Tools -> User-Defined Functions -> New or Go to “User-Defined Functions” in the folder, right click select New to create a UDF
- Enter name, type of function, return type, arguments required and of course the logic you want to wrap.
See below screen, Imagine you need to validate a file prior to the processing;
- Two amount fields are to be validated to ensure no non numerical values have put for these columns.
- Any record with non numeric value in this field to be rejected and to be written in a separate file or audit
- Rather coding it in an expression it would be more appropriate to get a user defined function for it.
How to use User Defined Function?
- Use the qualifier :UDF to call a user defined function.
Sample : See the screen below to see an user defined function call.