User Defined Functions

An SQL function that user defines and may invoke is known as a user-defined function (UDF). A UDF’s logic usually extends or improves SQL by adding capabilities that SQL lacks or cannot accomplish adequately, just like with built-in functions, you may invoke it from SQL. You may also encapsulate functionality with a UDF and call it again from different places within your code.

Example 1: Program based UDF

The table structure that will be utilized in the example is listed below.

Table1- EMPLOYEE

R REMP
EMPID 10S 0
EMPNAME 20A
EMPJOIND 8A
K EMPID

Create a procedure in NOMAIN module as GET0001 for retrieving a day for a date compile it using the 15 option from PDM.

ctl-opt nomain;
dcl-proc getdays export;
dcl-pi getdays char(3);
in_date char(8) const;
end-pi;
dcl-s day char(3);
dcl-s dowk packed(1:0);
dowk = %rem(%diff(%date(in_date:*iso0):d'0001-01-01': *days): 7);
select;
when dowk = 0;
day = 'MON';
when dowk = 1;
day = 'TUE';
when dowk = 2;
day = 'WED';
when dowk = 3;
day = 'THU';
when dowk = 4;
day = 'FRI';
when dowk = 5;
day = 'SAT';
when dowk = 6;
day = 'SUN';
endsl;
return day ;
end-proc;

Create a service program and bind the module with it.

CRTSRVPGM SRVPGM(PIOLIB/GETDATSV) MODULE(PIOLIB/GET0001) EXPORT(*ALL)

Now create a user-defined function (UDF) that can access this module.

create function rtvday( in_date char(8))
returns char(3)
language rpgle
deterministic
no sql
external name 'PIOLIB/GET0001(GETDAYS)'
parameter style general
program type sub
RTVDAY was created, changed, or dropped, but object not modified.

Using UDF in SQL query:

SELECT EMPID, EMPNAME, EMPJOIND , RETDAY(EMPJOIND) FROM PIOLIB.EMPLOYEE

Result Set

EMPID     EMPNAME      EMPJOIND       RETDAY ( EMPJOIND )
10,001     JAMES       20240319           TUE
10,001     KINGS       20220219           SAT
10,001     YORKS       20200110           FRI

Example 2: SQL based UDF.

create or replace function priority(indate date) returns char(7) 
language sql
begin
return(
case when in_date < current date then 'NONE'
when in_date <= current date +2 days then 'HIGH'
else 'MEDIUM'
end
);
end

Invoke the UDF from an SQL query as shown below:

SELECT EMPID, EMPNAME, EMPJOIND, EMPORDER, PRIORITY(EMPJOIND) FROM PIOLIB/EMPLOYEE

Result Set

EMPID    EMPNAME     EMPJOIND     EMPORDER    PRIORITY
10,001    EMP1       20221005     04/04/24        NONE