Automate your infrastructure to build, deploy, manage, and secure applications in modern cloud, hybrid, and on-premises environments.
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:
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.
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.
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
)
return
bigint
import java.util.
Date
;
long diff = shipdate.getTime() - orderdate.getTime();
diff / (24 * 60 * 60 * 1000);
} catch (Exception e) {
-1L;
}
end
select
ordernum, pub.
order
.custnum, substr(
Name
,1,20), substr(Address,1,20), Phone,
days_between(shipdate, pub.
.orderdate)
as
DAYS_DELAYED
from
pub.
inner
join
pub.Customer
on
pub.customer.custnum=pub.
.custnum
where
.orderdate) > 10
by
DESC
Ordernum CustNum substr(
,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
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.
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.
Subscribe to get all the news, info and tutorials you need to build better business apps and sites
Progress collects the Personal Information set out in our Privacy Policy and Privacy Policy for California Residents and uses it for the purposes stated in that policy.
You have the right to request deletion of your Personal Information at any time.
You can also ask us not to pass your Personal Information to third parties here: Do Not Sell My Info
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.
Copyright © 2021 Progress Software Corporation and/or its subsidiaries or affiliates.All Rights Reserved.
Progress, Telerik, Ipswitch, and certain product names used herein are trademarks or registered trademarks of Progress Software Corporation and/or one of its subsidiaries or affiliates in the U.S. and/or other countries. See Trademarks for appropriate markings.