It’s now time to get to know SQL user-defined functions and see how they can encapsulate your RPG code.

Although RPG stands for Report Program Generator, over the years, the language’s report-producing capabilities were neglected. Today they are clearly one of the least user- and programmer-friendly parts of RPG. This might seem a paradox, but given the fact that there are so many easy-to-use reporting tools today, IBM decided (wisely, in my opinion) that reporting was not a priority. However, when you have to produce complex reports that use data generated in real time and based on business rules, such as client debt risk or account balance, this can become quite a pain. The “halfway” approach is creating a program that generates a file with the report-ready data and then using a reporting tool, such as BIRT or Crystal Reports, to produce the final output.

All of this is necessary because there is nothing to bridge the gap between the RPG business logic and the SQL-accessible data. But wait—there’s a little something called a user-defined function (UDF) that behaves like a stored procedure and that you can use in a standard SELECT statement! A function, by definition, is a piece of code that has a return value—not an output parameter, like a procedure, but a full-blown return value, like RPG’s and SQL’s native functions.

Defining a UDF

Let’s start by seeing how to define a UDF. I’m going to use an RPG function named Clc_CustBalance that returns a DECIMAL(11, 2) value with the account balance amount for a given customer. The customer number is received as the input parameter, along with a reference date, to calculate the balance. Here’s the function’s interface:

*--------------------------------------------------------------------*

* Calculate customer balance (returns the customer balance,

*

* for a given customer number and reference date)

*-------------------------------------------------------------------*

P Clc_CustBalance...

P                 B                   Export                              

D Clc_CustBalance...

D                 PI           11P 2                                      

* Input parameters                                                      

D P_CustNbr                     9P 0 VALUE                              

D P_RefDate                     D   VALUE                  

Sorry for the fixed-format code! Now let’s see what the CREATE FUNCTION statement for this RPG function looks like:

CREATE FUNCTION CalculateCustomerBalance

(IN CustNbr DEC(9), IN RefDate DATE)

RETURNS DEC(11,2)

EXTERNAL NAME ‘MyLib/BRCst(Clc_CustBalance)’

LANGUAGE RPGLE

NOT DETERMINISTIC

NO SQL

PARAMETER STYLE General

There are a couple of differences compared to the CREATE PROCEDURE statement presented earlier. First, there aren’t any output parameters. Instead, there’s a RETURNS instruction followed by a data type. Notice that this data type and respective length match the RPG function’s return value. It’s a good idea to do this in order to avoid the extra processing required by data type conversions. Next, notice that I added two lines: NOT DETERMINISTIC and NO SQL. I explained both concepts in the previous article, so I won’t repeat myself here. These two lines are optional, but it’s a good practice to specify them at all times. In this particular case, I really have to, because NO SQL is not the default value for this option.

After creating this UDF, I can simply use it as a regular SQL function:

SELECT      CustNbr

            , CalculateCustomerBalance(CustNbr, DATE(‘2019-07-09’))

FROM        Customers;

This statement returns the customer number and respective account balance, using July 9 as the reference date. You might have to specify the library/schema name if the function is not in the library list, but other than that, you’re up and running. There’s also a neat trick that allows you to use RPG procedures as UDFs. Because RPG procedures don’t have a return value and UDFs require them, a few additional steps are required.

Using an RPG Procedure as a UDF

Let’s take the Rtv_ItemAvail procedure mentioned in the previous TechTip and transform it into a UDF. For that to happen, we need something that calls the RPG procedure from SQL and directs the output parameter value to the UDF’s return value. We already have something that can call the RPG procedure from SQL: the RetrieveItemAvailability stored procedure. The only problem to solve is directing its output to the UDF’s output. That takes a bit of procedure language, but don’t worry; it looks very similar to RPG. Here’s the RetrieveItemAvailability function’s code:

CREATE FUNCTION RetrieveItemAvailability

(IN ItemID CHAR(15), IN ItemQTY DEC(9,2))

RETURNS DATE

LANGUAGE SQL DETERMINISTIC

BEGIN  

DECLARE ItemAvail DATE;  

SET ItemAvail = DATE(‘0001-01-01’);  

CALL RetrieveItemAvailability (ItemID, ItemQTY, ItemAvail);  

RETURN ItemAvail;

END;

Due to its similarity to RPG, this should be easy to understand, but let me explain the methodology applied here:

• Declare and initialize a temporary variable.
• Call the stored procedure, passing the UDF’s input parameters and the temporary variable.
• The stored procedure updates the temporary variable.
• After the stored procedure’s call statement, return the contents of the temporary variable.

There you have it, your first procedural language UDF! Just let me stress two things. First, notice the LANGUAGE SQL DETERMINISTIC instruction in the function definition. This is not an RPG function, but an SQL one. Second, don’t confuse the UDF’s parameters with the stored procedure’s parameters. You need to remember to declare the temporary variable with the data type and length that the stored procedure requires.

Some data type conversion issues might arise when passing and receiving parameters between stored procedures and UDFs. This is something that has to be addressed individually and might not be easy to solve. If this happens, look for help in the RPG forums and user groups, as someone might know how to solve your specific problem.

It’s your turn now. Go ahead and experiment with your existing RPG programs and procedures, creating the SPs and UDFs that make will make them available to the “outside world” via SQL! Feel free to comment, suggest, and criticize this article or this series, using the Comments section below.

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

SQL 101: DDL Recap—From Physical Files to SQL Tables, Starting the Journey

TechTip: Generate and Email a Spreadsheet with SQL

Practical SQL: Journal Forensics Using SQL