top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

PowerCenter – User Defined Functions

+1 vote
642 views

User-defined functions extend the PowerCenter transformation language. You can create and manage user-defined functions with the PowerCenter transformation language in the Designer. You can add them to expressions in the Designer or Workflow Manger to reuse expression logic and build complex expressions. User-defined functions are available to other users in a repository.

Example: User want to format the 10 digit phone number as (999)999-9999. Create the user defined function FORMAT_PHONE_NUMBER with expression

'(' || SUBSTR(TO_CHAR(phone_no),1,3) || ')' ||SUBSTR(TO_CHAR(phone_no),4,3) || '-' || SUBSTR(TO_CHAR(phone_no),7,4)

After user creates the function, user can create the following expression in an Expression transformation to format the phone number:

:UDF.FORMAT_PHONE_NUMBER(IN_PHONE_NUMBER)

The user-defined function name is prefaced by :UDF. The port name is IN_PHONE_NUMBER. It is the argument of the expression.

Steps to implement the solution:
1. Place source file into the $PMSourceDir folder.
2. Open mapping into PowerCenter Designer; observe the navigator window user defined functions are listed.
enter image description here

Format_Phone_Number function accepts one argument and does the formatting as shown below.
enter image description here

Use of UDF can be observed in expression transformation as shown below.
enter image description here

enter image description here

3. Import workflow using Repository Manager. Select the appropriate folder from repository and resolve the conflicts by choosing suitable option.
4. Open the workflow in Workflow Manager. Assign the integration service in Workflow -> Edit -> Integration Service
5. Edit session and assign valid connection object for the source and target.
6. Execute the workflow and observe the target file.

posted Jun 20, 2014 by Shatark Bajpai

  Promote This Article
Facebook Share Button Twitter Share Button LinkedIn Share Button


Related Articles

SQL Server: User-Defined Functions and Types

Basics:
User defined functions fall into three categories; scalar, inline table-valued, and multi-statement table-valued.

A scalar user-defined function is used to return a single value based on zero or more parameters.

An inline table-valued UDF returns a table data type based on a single SELECT statement that is used to define the returned rows and columns. Unlike a stored procedure, an inline UDF can be referenced in the FROM clause of a query, as well as be joined to other tables. Unlike a view, an inline UDF can accept parameters.

A multi-statement table-valued UDF also returns a result set and is referenced in the FROM clause. Unlike inline table-valued UDFs, they aren’t constrained to use a single SELECT statement within the function definition and instead allow multiple Transact-SQL statements in the body of the UDF definition in order to define a single, final result set to be returned.

Creating Scalar User-Defined Functions:

-- Create a function to check for any suspicious behaviors
-- from the application
CREATE FUNCTION dbo.udf_CheckForSQLInjection
(@TSQLString varchar(max))
RETURNS BIT
AS
BEGIN
DECLARE @IsSuspect bit
-- UDF assumes string will be left padded with a single space
SET @TSQLString = ' ' + @TSQLString
IF (PATINDEX('% xp_%' , @TSQLString ) <> 0 OR
PATINDEX('% sp_%' , @TSQLString ) <> 0 OR
PATINDEX('% DROP %' , @TSQLString ) <> 0 OR
PATINDEX('% GO %' , @TSQLString ) <> 0 OR
PATINDEX('% INSERT %' , @TSQLString ) <> 0 OR
PATINDEX('% UPDATE %' , @TSQLString ) <> 0 OR
PATINDEX('% DBCC %' , @TSQLString ) <> 0 OR
PATINDEX('% SHUTDOWN %' , @TSQLString )<> 0 OR
PATINDEX('% ALTER %' , @TSQLString )<> 0 OR
PATINDEX('% CREATE %' , @TSQLString ) <> 0OR
PATINDEX('%;%' , @TSQLString )<> 0 OR
PATINDEX('% EXECUTE %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% BEGIN %' , @TSQLString )<> 0 OR
PATINDEX('% CHECKPOINT %' , @TSQLString )<> 0 OR
PATINDEX('% BREAK %' , @TSQLString )<> 0 OR
PATINDEX('% COMMIT %' , @TSQLString )<> 0 OR
PATINDEX('% TRANSACTION %' , @TSQLString )<> 0 OR
PATINDEX('% CURSOR %' , @TSQLString )<> 0 OR
PATINDEX('% GRANT %' , @TSQLString )<> 0 OR
PATINDEX('% DENY %' , @TSQLString )<> 0 OR
PATINDEX('% ESCAPE %' , @TSQLString )<> 0 OR
PATINDEX('% WHILE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENDATASOURCE %' , @TSQLString )<> 0 OR
PATINDEX('% OPENQUERY %' , @TSQLString )<> 0 OR
PATINDEX('% OPENROWSET %' , @TSQLString )<> 0 OR
PATINDEX('% EXEC %' , @TSQLString )<> 0)
BEGIN
SELECT @IsSuspect = 1
END
ELSE
BEGIN
SELECT @IsSuspect = 0
END
RETURN (@IsSuspect)
END
GO

Creating Inline User-Defined Functions:

CREATE FUNCTION dbo.udf_ReturnEmployeeAddress
( @EmployeeID int)
RETURNS TABLE
AS
RETURN (
SELECT AddressLine1, City, StateProvinceID, PostalCode
FROM Person.Address a
INNER JOIN HumanResources.EmployeeAddress e ON
a.AddressID = e.AddressID
WHERE e.EmployeeID = @EmployeeID )
GO

Creating Multi-Statement User-Defined Functions: Creates a UDF that returns a string array as a table result set

CREATE FUNCTION dbo.udf_ParseArray
( @StringArray varchar(max),
@Delimiter char(1) )
RETURNS @StringArrayTable TABLE (Val varchar(50))
AS
BEGIN
DECLARE @Delimiter_position int
IF RIGHT(@StringArray,1) != @Delimiter
SET @StringArray = @StringArray + @Delimiter
WHILE CHARINDEX(@Delimiter, @StringArray) <> 0
BEGIN
SELECT @Delimiter_position =
CHARINDEX(@Delimiter, @StringArray)
INSERT @StringArrayTable
VALUES (left(@StringArray, @Delimiter_position - 1))
SELECT @StringArray = STUFF(@StringArray, 1,
@Delimiter_position, '')
END
RETURN
END
GO
READ MORE
...