SQL Functions You Didn’t Know You Had, Part 1

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!”)

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot