Advantage Developer Zone

 
 
 

SQL User Defined Functions

Tuesday, October 10, 2006

With the release of Advantage Version 8.1, the SQL engine has been enhanced to allow developers to create User Defined Functions (UDFs) that can be used within SQL statements. These custom functions are defined inside a data dictionary and are written using SQL Persistent Stored Module code. Once the UDFs have been defined, any SQL statement that is executed on a connection to the data dictionary where the UDFs are defined can use them.

UDF Background

Just what is a User Defined Function? A UDF is an SQL script that is stored within a database. UDFs can then be used within other SQL statements in the same way that built-in SQL scalar functions are used.

UDFs are structured much like SQL Scripted Stored Procedures in that they accept zero to many arguments and return a value. Unlike Stored Procedures, UDFs may only return a single value.

Creating a UDF

UDFs can be created using Advantage Data Architect (ARC) or by using the SQL Create Function command. You must be connected to a data dictionary and have sufficient rights to create functions within the dictionary.

Many databases store names of employees, customers, and others. Generally these names are stored in separate fields for the given (first name), surname (last name), middle initial, etc. This format is useful for providing flexibility in sorting the table, but is not necessarily the way you want to present the data to the user. In most cases, SQL statements are written that concatenate the name into a more familiar display format. Creating a function to display the name in a full name format makes a lot of sense.

To create this function, called FULLNAME, you can use the following query:

CREATE FUNCTION FullName( Last CHAR(25), First CHAR(25))
  RETURNS CHAR(80)
  BEGIN
     RETURN Trim(Last) + ', ' + Trim(First);
  END; 

 

This function will be created in the data dictionary and can now be used by any query run against that dictionary. The following example demonstrates the use of this function:

SELECT ID, FullName(LastName, FirstName) as Name,
  DateOfHire, Address, City, State, ZipCode FROM Employee

 

The resulting data looks like this:

 

Altering UDFs

Once a UDF has been created in the data dictionary, it can be modified using ARC or through the use of the ALTER FUNCTION SQL statement. Suppose we wanted to display the middle name as part of the full name. We would simply modify our function to accept another parameter like so:

ALTER FUNCTION FullName( Last CHAR(25), First CHAR(25), Middle CHAR(25))
  RETURNS CHAR(80)
  BEGIN
    RETURN Trim(Last) + ', ' + Trim(First) + ' ' + IfNull(Trim(Middle), '');
  END;

 

Notice that the function now accepts a third argument, middle, and also makes use of the IFNULL function. Adding fields together using SQL will return NULL if any of the fields being added together are NULL. The IFNULL function substitutes a value, in this case a single space, if the middle name field contains a NULL. This ensures that we always get back our first and last names. You may wish to use IFNULL on all three arguments to ensure that a value is always returned.

Using this function would produce the following results, notice that Norm Grisham does not have a middle name:

UDFs and Packages

UDFs can be grouped together using Packages. Packages are another object within the data dictionary and are created using ARC or via a CREATE PACKAGE command. Packages are simply a container for logically grouping functions together and do not require any special options. The following statement creates a package:

CREATE PACKAGE MyFunctions

 

To create functions within a package, simply right-click on the package in ARC and choose Create Function. Alternately, precede the function name with the package name followed by a period ( . ) when using a CREATE FUNCTION statement. For example:

CREATE FUNCTION MyFunctions.Fullname …

 

To use a function contained within a package, use the same dot notation used to create the function.

SELECT ID, MyFunctions.FullName(LastName, FirstName) as Name,
  DateOfHire, Address, City, State, ZipCode FROM Employee

 

Summary

SQL User Defined Functions allow you to create custom functions using the SQL Scripting language. These functions can be used with any SQL statement and run against the data dictionary where the UDFs have been defined. UDFs help modularize your code, which makes it easier to maintain and improves readability. Other benefits of UDFs include additional abstraction, use of server-side processing, and centralization of business logic and rules.