Follow

How to use a User Defined Function

To use a User Defined Function, you create a function with parameters. Executes the function with set parameters and uses this in your project.

In my example I have a CSV file that is set up in a way so I cant use the normal single text file data source. It is set up like this.

DW_Id,Company;Business Posting Group;Business Posting Group Description;City;Country Code;Country Name;Customer Posting Group;Name;No;Salesperson Code;Salesperson Name;State;IncrementalTimeStamp;DWBatch;DWSourceCode;DWTimeStamp|1,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Miami;US;USA;FOREIGN;Spotsmeyer's Furnishings;01121212;JR;John Roberts;FL;14-06-2018 09:42;25;Nav;14-06-2018 09:42|2,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Chicago;US;USA;FOREIGN;Progressive Home Furnishings;01445544;JR;John Roberts;_;14-06-2018 09:42;25;Nav;14-06-2018 09:42|3,Cronus International Ltd.;EXPORT;Other customers and vendors (not EU);Atlanta;US;USA;FOREIGN;New Concepts Furniture;01454545;JR;John Roberts;GA;14-06-2018 09:42;25;Nav;14-06-2018 09:42|

It contains three different delimiters. , ; and |. There is a link to the file in the bottom of this.

I have added it to my project by making a custom table. that contains a single nvarchar(max) field. I then copied the data from the csv file into it as custom data.

8.PNG

What I want to do, is to create a SPLIT function, to do this work for me. I searched the web and found one that could be used.

CREATE FUNCTION [dbo].[fn_Split]
(
@String nvarchar(max),
@Delimiter nvarchar(1)
)
RETURNS @TempTable TABLE (whichitem int identity, items nvarchar(max))
as
begin

declare @intPosition int
,@vchElement nvarchar(max)

if len(@String) < 1 or @String is null
return

set @intPosition = 1
while @intPosition <> 0
begin
set @intPosition = charindex(@Delimiter, @String)
if @intPosition <> 0
set @vchElement = left(@String, @intPosition - 1)
else
set @vchElement = @String

if len(@vchElement) > 0
insert into @TempTable(items) values(@vchElement)

set @String = right(@String, len(@String) - @intPosition)
if len(@String) = 0 break
end

return
end

It has two parameters, a text and a delimiter. What you receive is the data called items and a id called whichitem.

To add it to a User Defined Function you start by adding one to your project.

1.PNG

Then you can write your function, or copy and paste it in. You can use parameters and project variables, if necessary. Remember that the name of the function must be the same in the code and as the name itself.

2.PNG

To use the function you can make a insert into script action, a custom view, a stored procedure or a custom table insert.

I made a custom table containing the first two fields of the file. The ones with a , field delimiter and a |.

3.PNG

Here below is a insert into script action script that adds to that table.

DECLARE @String nvarchar(max) = (SELECT [CustData] FROM [Data Cust])

INSERT INTO [Split 1_R]
(
[ID],
[Data]
)
SELECT MAX(CASE WHEN f2.whichitem = 1 THEN f2.items END) AS [ID],
MAX(CASE WHEN f2.whichitem = 2 THEN f2.items END) AS [Data]
FROM [fn_split](@String, '|') AS f1
CROSS APPLY [fn_split](f1.items, ',') AS f2
WHERE f1.whichitem <> 1
GROUP BY f1.whichitem

I start by declaring a @String variable and filling it with the data from my file. Then I change the CRLF delimiter to a |. Then I use the split function with the data and the | as delimiter, to get the rows. Then I use cross apply on the function with , as the delimiter, to get the columns. Finally I use the witchitem field to get the column id and its data. The where clause is to remove the header line and the Group By is to get it all on one row.

Here is how I use this in a parametrized script. Notice that I can use the user defined function as a parameter.

4.PNG

When you set this to run as a pre step before data cleansing and deploy/execute everything, you will get this.

5.PNG

So as you can see it still needs to be split out further. This is where the bonus of having it as a function comes in. You can just use it again.

Below is a custom view script that splits it a second time.

CREATE VIEW [dbo].[Split 2] AS
SELECT f1.[ID] AS 'DW_Id',
MAX(CASE WHEN f2.whichitem = 1 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_Account',
MAX(CASE WHEN f2.whichitem = 2 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Gen. Bus. Posting Group',
MAX(CASE WHEN f2.whichitem = 3 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Business Posting Group',
MAX(CASE WHEN f2.whichitem = 4 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'City',
MAX(CASE WHEN f2.whichitem = 5 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Country/Region Code',
MAX(CASE WHEN f2.whichitem = 6 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Country Name',
MAX(CASE WHEN f2.whichitem = 7 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Customer Posting Group',
MAX(CASE WHEN f2.whichitem = 8 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Name',
MAX(CASE WHEN f2.whichitem = 9 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'No.',
MAX(CASE WHEN f2.whichitem = 10 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Salesperson Code',
MAX(CASE WHEN f2.whichitem = 11 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'Salesperson Name',
MAX(CASE WHEN f2.whichitem = 12 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'IncrementalTimeStamp',
MAX(CASE WHEN f2.whichitem = 13 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_Batch',
MAX(CASE WHEN f2.whichitem = 14 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_SourceCode',
MAX(CASE WHEN f2.whichitem = 15 THEN CASE WHEN f2.items <> '_' THEN f2.items ELSE NULL END END) AS 'DW_TimeStamp'
FROM [Split 1] AS f1
CROSS APPLY [fn_split](f1.[Data], ';') AS f2
GROUP BY f1.[ID]

This is more or less the same procedure as before. I pull the data from the Split 1 table and use cross apply and the split function on the data field to get the columns filled.

Here below is how it is looking in a parametrized custom view.

6.PNG

So in general with functions, you need to figure out what parameters you need and where you want to use them. Here below is the final result. As you can see it now resembles a normal table.

7.PNG

 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.