Informatica Powercenter – User Defined Functions

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.

UDF code

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.

IIF
(
ISNULL( TEXT_INPUT )
, 'THIS IS A NULL ROW'
, UPPER( LTRIM( RTRIM( TEXT_INPUT ) ) )
)

Here’s our UDF in use within our mapping:

UDF inside epxression transformation

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:

our UDF worked!

References

https://docs.informatica.com/data-integration/powercenter/10-4-0/designer-guide/working-with-user-defined-functions/working-with-user-defined-functions-overview.html

https://youtu.be/ej9DnVrSbro – How to Create User Defined Functions in PowerCenter

Publicado por Pedro Carvalho

Apaixonado por análise de dados e Power BI

Um comentário em “Informatica Powercenter – User Defined Functions

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

%d blogueiros gostam disto: