Re: SQL Functions in RPG

date: Tue, 3 Nov 2015 16:51:24 -0600
from: "Englander, Douglas" <Douglas_Englander@xxxxxxxxxxxxx>
subject: Re: SQL Functions in RPG


Mike,

I have an RPG program that reads each record in a subset of a file
(positioning via SETLL) and as it reads it determines whether or not it
should process the record further. The program runs for a long time, of
course, and I was hoping to speed it up a bit using SQL.

One of the criteria it checks is up to 5 values of a field that a user
could key in. Those values could be a "wildcard" type value. The logic in
the RPG to process this is quite original. In my view, it is way too
complicated for a plain SELECT statement, so that is why I wanted to use a
function that returns a "0" or "1" to tell SQL whether to include the row
or not. I have done this before with a similar function in service program
and it works very well. But since this logic is unique to this program
only, I did not want to encapsulate it into a service program, so that is
why I was wondering if there was a way to create a SQL function that
references a subprocedure in a program.

Based on what I have read, I am thinking of putting a "CREATE FUNCTION" in
the RPG program in *INZSR, referencing that function in the SELECT
statement, and then dropping it when I am done with the program (at LR
time).

Thank you for all the replies!!

Doug

Hi Doug,

It sounds like you've got a good plan for now.

In the long term, keep working on your SET based SQL skills. With enough
practice and some well designed and placed SQL functions, SQL can handle
close to anything RPG can. A couple of areas where SQL falls short that
come to mind are controlling user interfaces, and tasks requiring
pointers. Once you get those skills to the point where you feel
comfortable enough to use SQL to tackle a more complex task like the one
you're working on now, you'll look back at some of your older code and find
yourself wishing it relied on SQL to do more of the work. The benefit of
doing so is smaller code volume, faster run time, greater clarity, and
reduced chance for defects.

Mike