Make an SQL UDF Return Null
August 14, 2002 Timothy Prickett Morgan
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?
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.
ADVANCED SYSTEMS CONCEPTS
SEQUEL meets all your iSeries and AS/400 data access needs in a single, integrated solution:
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.