< Registering an RPG procedure as an SQL Function

Service program procedures can be registered as SQL User Defined Functions.

RPG as an SQL Function

ILE has benefits to offer both RPG and SQL allowing both languages to share common functions. Service programs can be created from procedures which may be used in both SQL and RPG. Re-usable code leverages existing processes to speed future application development. A simple example is a procedure that performs a simple conversion on a column/field from one format to another format.

Creating a UDF

The code below describes a procedure in a service program. It was created to accept a date-type variable as input as well as a date format (*ISO, USA, etc.) which will be used to format the decimal value that the procedure returns. (Yes, there is a BIF to perform this, but the procedure contains different rules and validation regarding what is passed back. For example, the procedure may construct a 6-digit date format that is outside the IBM’s standard date window.)


Dcl-Proc Convert_Date_to_Dec Export;
  Dcl-PI Convert_Date_to_Dec zoned(8:0);
    in_Date date Const;
    in_Format varChar(5) Const;
End-PI;

The same function can be made available for use in SQL by creating a User Defined Function (UDF). UDF’s are not limited to SQL code (though they may be created with SQL). A service program procedure written in RPG can quite easily become an SQL UDF. All that needs to be done in this case is to register the service program procedure as a function. Create Function supplies the necessary argument and defines the parameter style for the new function. (A row will be entered in QSYS2.SYSROUTINE)


CREATE FUNCTION CONVERT_DATE_TO_DEC ( in_date DATE, in_from_format VarChar(5) )
RETURNS NUMERIC(8,0)
LANGUAGE rpgle
DETERMINISTIC
NO SQL
EXTERNAL NAME 'MYLIB/DATE_SP(CONVERT_DATE_TO_DEC)'
PARAMETER STYLE General;

An RPG program (bound to the DATE_SP service program, where the procedure is defined) can access the procedure to convert a date into a zoned decimal value (ISO format) in a manner consistent with the date rules coded. The RPGLE code looks exactly as expected, using the EVAL to assign the numeric date variable to the specified date format.


today = %date();
dateR1 = Convert_Date_To_Dec( Today : '*ISO' );
yesterday = today - %days(1);
dateR3 = Convert_Date_To_Dec( yesterday : '*MDY' ) ;

Since a User Defined Function (UDF) was constructed to invoke the same procedure, an SQL statement may be issued—in a SET or SELECT operation, for example, to access the same procedure. The variable returned is consistent with the same format and rules applied to the request in RPG code.


tomorrow = today + %days(1);

Exec SQL
set :Date2 = Convert_Date_to_Dec( :tomorrow , :ISOVAR );

Exec SQL
SELECT * FROM ap_docFile WHERE CONVERT_DATE_TO_DEC(docDate,'*USA') = 10032014

This example is very simplistic, but does illustrate an easy method of sharing common functions between SQL and RPG via service programs. Check out IBM’s Knowledge Center for more information:

IBM Knowledge Center