Extend Your SQL with User-Defined Functions in OpenEdge 11.7

With user-defined functions, OpenEdge database developers can extend their programming to validate business logic, use less bandwidth and more.

OpenEdge SQL 11.7 allows you as database developers to create your own routines. These routines, called User-Defined Functions (UDF), can accept parameters, perform custom actions and return results. These are required especially when it comes to business logic, complex algorithmic calculations and custom actions. They also help reduce network traffic.

UDFs help the user extend the SQL functionality. You can write UDFs in Java to provide functionality that is not available in SQL or SQL built-in functions by default.

UDFs can be added in an SQL statement to a SQL function or to an expression. For example, you can use the UDFs in the following cases:

The select list of a SELECT statement
The condition of a WHERE clause
ON CLAUSE, ORDER BY, and GROUP BY clauses
The VALUES clause of an INSERT statement
The SET clause of an UPDATE and DELETE statement

With UDFs, you can express your business logic and execute the business logic in the SQL engine, from where it can be called in your SQL queries.

Prerequisites

You must create UDFs as top-level functions before they can be used within an SQL statement. To use a user function in an SQL expression, you must own or have EXECUTE privileges on the user function.

Benefits of UDFs

Extends programming richness via user code - Your database can now do more than only process data. With UDFs, databases can now also validate business logic and perform custom actions.
Optimizes network traffic - UDFs allow data manipulation such that you do not need a lot of interaction with the database and you can optimize your results based on your business logic within the database. This reduces network bandwidth utilization.
Reuse code, which can be called akin to stored procedures with business logic.

Limitations of UDFs

When called in SQL queries, UDFs are executed only in a single thread. They follow a serial execution plan. Hence, if the business logic inside UDFs would work better in a multi-threaded environment, it would be better to not implement it as a user-defined function, since it would lead to overall performance degradation.
UDFs cannot change database state, so there might be usage constraints if you are attempting to change the database information inside your business logic.
The OpenEdge 11.7 release only supports scalar UDF.

When to Choose a UDF?

You want a single value as the result. For example, total cost of a business trip, total project cost in man-days, or the birth date of a person.
The return type can be any valid SQL type except arrays and lobs.

Example of an OpenEdge SQL User-Defined Function

Now that you know the benefits and limitations of a UDF, let’s walk through an example. We’ll create a function called days_between and see how to use it in a SQL select statement. This UDF accepts two input parameters named shipdate and orderdate which are of type date, calculates difference between them and returns a bigint value as output from the UDF.

days_between (date, date): This function provides the difference between two dates.

OpenEdge SQL has a similar Months_Between scalar function that gives you days_between functionality.

create function days_between(shipdate date, orderdate date)

return bigint

import

import java.util.Date;

begin

try {

long diff = shipdate.getTime() - orderdate.getTime();

return diff / (24 * 60 * 60 * 1000);

} catch (Exception e) {

return -1L;

}

end



select

ordernum, pub.order.custnum, substr(Name,1,20), substr(Address,1,20), Phone,

days_between(shipdate, pub.order.orderdate) as DAYS_DELAYED

from pub.order inner join pub.Customer

on pub.customer.custnum=pub.order.custnum

where days_between(shipdate, pub.order.orderdate) > 10

order by days_between(shipdate, pub.order.orderdate) DESC;

Ordernum CustNum substr(Name,1,20) substr(Address,1,20) Phone DAYS_DELAYED



174 64 Hamahakkimies Ky Tainionkoskenkatu 20 (50) 374 3131 325

157 42 Stall-turn Flying Hurricane Lane 0702 258 133 260

Save Time and Bandwidth

With user-defined functions in OpenEdge 11.7, you can extend your SQL functionality to save time, gain capabilities and reduce bandwidth use. You can read more about UDFs in our documentation, and don’t forget to check out everything that’s new in OpenEdge 11.7.

OpenEdge SQL

Jyothi Sundaragiri

Jyothi is a Sr. Engineer at Progress, working on OpenEdge SQL for the last 6 years. She has strong knowledge of SQL and works on various features including User Defined Functions, Multi Tenancy, Table Partitioning and Scalar Subquery support. Prior to working at Progress, she worked as a Software Developer at InMage systems.