Pages

Thursday, October 20, 2016

Creating User Defined Functions using only SQL

creating trigger in 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.

No comments:

Post a Comment

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.