Make an SQL UDF Return Null

August 14, 2002 Timothy Prickett Morgan

Hey, Ted:

In the October 19, 2001 Midrange Guru, OS/400 Edition, you showed how to use an RPG subprocedure in a service program as an SQL user-defined function. Can you tell me how to write a similar function that can return a null value?

— Marty

The key to returning a null value is in the parameter style (the parameter-passing convention) that you use. I like the SQL convention, but there are others that will work.

The SQL convention passes the following parameters:

• All of the input parameters specified in the create function SQL command
• The output parameter (the result returned by the function)
• The null indicators for the input parameters
• The null indicator for the output parameter
• The SQL state
• The fully qualified function name
• The specific name
• The message text

You can read more about these parameters in the iSeries Information Center’s document, “DB2 Universal Database for iSeries SQL Reference,” located at http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/info/db2/rbafzmst85.htm .

Here’s an example. Input to the function is an alphanumeric order number. Output is a ten-character responsibility code. The RPG subprocedure that implements the function is to look for the order in an ORDERS file.

A                                      UNIQUE
A          R ORDERREC
A            ORDERNBR       8
A            RESPONSIBL    10          ALWNULL
A          K ORDERNBR

If it finds the order and the responsibility code is not null, the function is to return the responsibility code. If the order is not in the file, or if it is there but the responsibility code is null, the function should return a null value.

Here’s the RPG source code for the module, which is stored in member ORDER34 of file MYLIB/SRC.

 * Implement SQL UDF GetResp                  
                                               
H nomain alwnull(*usrctl) 
                                               
Forders    if   e           k disk
                                               
D GetResp         pr                          
D   OrderNbr                     8a   varying
D   Resp                        10a
D   OrderNbrNull                 5i 0
D   RespNull                     5i 0
D   SqlState                     5a
D   FuncName                   517a   varying
D   SpecificName               128a   varying
D   MsgText                     70a   varying
 
P GetResp         b                   export
D                 pi
D  POrderNbr                     8    varying
D  PResp                        10a
D   OrderNbrNull                 5i 0
D  PRespNull                     5i 0
D   SqlState                     5a
D   FuncName                   517a   varying
D   SpecificName               128a   varying
D   MsgText                     70a   varying
                                                       
C                   eval      PRespNull = -1
C                   eval      OrderNbr = POrderNbr
C     OrderNbr      chain     orderrec
C                   if        %found
C                   if        not %nullind(responsibl)
C                   eval      PResp = responsibl
C                   eval      PRespNull = *zero
C                   endif
C                   endif
P                 e

The second and fourth parameters are used to pass the information back to the function. The second parameter is the responsibility code. The fourth parameter is the null indicator for the responsibility code. The subprocedure must set it to either zero (to indicate that the second parameter is not null) or -1 (to indicate that the second parameter is null.)

Create a module and a service program.

CRTRPGMOD MODULE(MYLIB/ORDER34) +
   SRCFILE(MYLIB/SRC) +
   SRCMBR(ORDER34)       
CRTSRVPGM SRVPGM(MYLIB/ORDER34) +
   MODULE(MYLIB/ORDER34) +
   EXPORT(*ALL)

To create the function, use the following SQL command.

create function mylib/getresp (varchar(256)) 
   returns char(10)                            
   returns null on null input                  
   language rpgle                              
   external name 'MYLIB/ORDER34(GETRESP)'     
   no sql              
   no external action  
   parameter style sql 
   not deterministic   

Notice that the next-to-the-last line in the SQL command properly sets the parameter style. The external name indicates that the subprocedure name is GETRESP, in module ORDER34 of library MYLIB.

— Ted

Sponsored By ADVANCED SYSTEMS CONCEPTS

SEQUEL meets all your iSeries and AS/400 data access needs in a single, integrated solution: Windows, Web or host user interfaces Convert AS/400 data into PC file formats E-mail or FTP query results, reports and spool files Run-time prompted queries and reports for end users IF-THEN-ELSE logic in queries and reports Report, form and label formatting second to none Easily convert date fields, character-to-numeric, numeric-to-character and other data manipulation SORT or JOIN using a calculated field Quick summarization of data with Tabling function Run multiple SEQUEL requests as one with the SEQUEL Scripting function OLAP Business Intelligence at a fraction of the cost of comparable solutions Take 6 minutes to view a SEQUEL ViewPoint ScreenCam movie to see how simple Windows-based AS/400 and iSeries data access can be! In just a few short minutes, you can find out ways to make your job easier and improve data access throughout your organization. Download the ViewPoint movie here . For more information or a FREE trial of SEQUEL, call 847/605-1311 or visit Advanced Systems Concepts.