SQL Functions You Didn’t Know You Had, Part 1
October 22, 2014 Ted Holt
What if I told you that you may have some potential powerful SQL functions on your system that you are not aware of? What if I told you that I have no idea what those SQL functions are named? What if I told you that you could easily use existing RPG routines in SQL queries? Would you be interested?
(Take a tip from the Little Rascals (AKA Our Gang) and say, “And how!”)
It’s easy to make SQL functions out of the subprocedures in service programs. To illustrate, here’s the source code for an RPG module from which a service program is built.
ctl-opt nomain; /copy prototypes,addr dcl-proc GetCity export; dcl-pi *n varchar(24); inAddress varchar(48) const; end-pi; return %subst(inAddress: 1: %scan (',': inAddress) - 1); end-proc GetCity; dcl-proc GetState export; dcl-pi *n char(2); inAddress varchar(48) const; end-pi; return %subst(inAddress: %scan (',': inAddress) + 2: 2); end-proc GetState; dcl-proc GetZip export; dcl-pi *n varchar(10); inAddress varchar(48) const; end-pi; return %subst(inAddress: %scan (',': inAddress) + 5); end-proc GetZip;
You don’t have to use free-form RPG, as I did when putting together this illustration. Fixed-format RPG works just as well.
I based this illustrative module on a project on which I worked recently. I had to work with data in which city, state, postal code, and country name were stored together as one value. I had to come up with a way to separate the parts of the address. This example is a vastly simplified version that extracts city, state, and ZIP code (the U. S. postal code). In this version, I assume that the city is followed by a comma and space, then the state, one space, and finally the ZIP code.
I create the module.
CRTRPGMOD MODULE(MYLIB/ADDR) SRCFILE(MYLIB/QRPGLESRC)
I create a service program from the module.
CRTSRVPGM SRVPGM(MYLIB/ADDR) EXPORT(*ALL)
Now my programs can bind to the service program to use the GetCity, GetState, and GetZip subprocedures. Wonderful! This is what ILE is all about.
Let’s take it a step further. By creating SQL functions over those subprocedures, I can use this same code in SQL queries. Here is the command to create the GetCity function. (I’ll leave it to you to create the GetState and GetZip functions if you’re interested.)
create or replace function GetCity (inString varchar(48)) returns varchar(24) language rpgle parameter style general deterministic no sql returns null on null input no external action not fenced no final call allow parallel no scratchpad external name 'MYLIB/ADDR(GETCITY)'
Here’s a query that uses these new SQL functions.
select key, CityStateZip, GetCity(CityStateZip), GetState(CityStateZip), GetZip(CityStateZip) from BigData
And here are the results:
KEY CITYSTATEZIP GETCITY GETSTATE GETZIP === ============================= ============== ======== ========== 1 Sand Francisco, MS 38888-1492 Sand Francisco MS 38888-1492 2 Lost Angeles, TX 77777 Lost Angeles TX 77777 3 New Yolk, CA 99999-4949 New Yolk CA 99999-4949
Now think about the service programs in your shop. Each exported subprocedure that returns a value is a potential SQL function. Isn’t that great!? (Take a tip from the Little Rascals and exclaim, “You’re tellin’ me!”)