Creating User Defined Functions using only SQL

In my previous post, Creating User Defined Functions to make my SQL statements easier, I gave example of how to create SQL User Defined Functions, UDF, using RPG. In this post I am going to show how I can create an UDF made up entirely of SQL.

I have created this UDF to concatenate the parts of the employee's name together. My example Employee Master file, EMPMST, has the following fields:

A R EMPMSTR A LASTNME 30A A FIRSTNME 20A A MIDINITL 1A

When I look in the file I see:

LASTNME FIRSTNME MIDINITL HUTCHINSON SIMON D CRUZ ANNA M SMITH JOHN D GRAY DARNESHA A GUPTA RANJIT

Everyone wants to see the employees' names as: Last name, First name I. This is easy to do using a CONCAT, but it is a pain to have to do it every time I need the employees' details. Well, UDF is going to come to my rescue, again, see below:

01 CREATE OR REPLACE FUNCTION MAKENAME ( 02 LastName char(30), 03 FirstName char(20), 04 MiddleInitial char(1) 05 ) 06 RETURNS VARCHAR(55) 07 LANGUAGE SQL 08 DETERMINISTIC 09 BEGIN 10 DECLARE FullName VARCHAR(55) ; 11 SET FullName = RTRIM(LastName) CONCAT ', ' CONCAT 12 RTRIM(FirstName) CONCAT ' ' CONCAT 13 MiddleInitial ; 14 RETURN FullName ; 15 END ;

The obvious difference is that it is all in one. Unlike with RPG, I do not have to have a separate object to use for the definition of my function.

Line 1: it should come as no surprise that the function's definition starts with CREATE OR REPLACE FUNCTION.

Lines 2 – 5: This function has three incoming parameters. These parameters need to separated by a comma and enclosed within parentheses/brackets ( ( ) ).

Line 6: My returned value is defined as a variable length character field.

Line 7: The "doing" part of this function is written in SQL.

Line 8: This is DETERMINISTIC i.e. if the same three parameters are passed to the function it somehow remembers to return the same result.

Line 9: The BEGIN indicates the start of "doing" part.

Line 10: I start by defining a variable with the same attributes as I will be using to return to whatever called this function.

Lines 11 - 13: I use the SET option to move the results of my concatenated string into the result variable FullName. The rest of these lines are right trimming, RTRIM, of the names and concatenating, CONCAT, them together.

Line 14: I can now return the value held in FullName to whatever called this function.

Line 15: I need to end the "doing" part of the function.

I can now use this function to concatenate the name field together every time I need to:

SELECT MAKENAME(LASTNME,FIRSTNME,MIDINITL) FROM MYLIB.EMPMST MAKENAME HUTCHINSON, SIMON D CRUZ, ANNA M SMITH, JOHN D GRAY, DARNESHA A GUPTA, RANJIT

 

I hope this post and the previous one, describing how to create functions using RPG, will prove useful to you and help you to create your own.

 

This article was written for IBM i 7.3, and should work for earlier releases too.