RE: Stored Procedures in RPG

[rob@xxxxxxxxx]

Joe,

Regarding a UDF sample:

I recently sent this out on the midrange-l list but I'll be happy to send
it again.  As someone pointed out on the other list, this could have been
done without a UDF because it is so simple.  But it makes for a good
example - especially for someone familiar with the files of which I speak.

I am not knocking triggers.  I use them and they have their place.  What I
want to bring up is that User Defined Functions are cool and ought to be a
tool in your tool box.

Let's say you use a trigger that when a record is read, you replace the
value on a field.  Because the stored value is out of date (pricing?) Or
you want to make it simple for your users.  Let me show you an example.
BPCS has a file called IIM for item master.  Some of the fields are:
IPROD
IDESC
IADJ
IRCT
IISS
IOPB

Notice, no ONHAND.  You calculate ONHAND=IOPB+IRCT+IADJ-IISS
Really fun for Query, eh?
You could write a read trigger that would store the onhand into a field in
the file that you are not using.

Now suppose I the following in RPGLE:
     H NOMAIN
     D*--------------------------------------
     D* Prototype for procedure: ONHAND
     D*--------------------------------------
     D ONHAND          PR            11P 3
     D OpeningBal                    11P 3 CONST
     D Receipts                      11P 3 CONST
     D Adjustments                   11P 3 CONST
     D Issues                        11P 3 CONST

     P*--------------------------------------
     P* Procedure name: ONHAND
     P* Purpose:
     P* Returns:        On hand balance
     P* Parameter:      OpeningBal => Opening Balance
     P* Parameter:      Receipts => Receipts
     P* Parameter:      Adjustments => Adjustments
     P* Parameter:      Issues => Issues
     P*--------------------------------------
     P ONHAND          B                   EXPORT
     D ONHAND          PI            11P 3
     D OpeningBal                    11P 3 CONST
     D Receipts                      11P 3 CONST
     D Adjustments                   11P 3 CONST
     D Issues                        11P 3 CONST

     D* Local fields
     D retField        S             11P 3

      /free
       retField=OpeningBal+Receipts+Adjustments-Issues;
       return retField;
      /end-free

     P ONHAND          E

Then I do the following:
CRTRPGMOD MODULE(ROB/ONHAND) SRCFILE(ROB/QPGMSRC)
CRTSRVPGM SRVPGM(ROB/ONHAND) EXPORT(*ALL)
STRSQL
create function ROB/ONHAND (DEC (11,3), DEC (11,3), DEC (11,3),
                            DEC (11,3))
returns DEC (11,3)
language rpgle
deterministic
no sql
returns null on null input
no external action allow parallel
simple call
external name 'ROB/ONHAND(ONHAND)'


To test:
select iprod, onhand(iopb, irct, iadj, iiss)
from iim
Item Number              ONHAND
"E"18BLK                   .000
"E"18PPL                   .000
"E"18RED                   .000
"E"18WHITE                 .000
#1                       72.000
#2                       72.000
#231GUDEBR            1,412.000

Ok, now a valid test, but where's the payback?

create view rob/iimx as
select iprod,
onhand(iopb, irct, iadj, iiss) as ONHAND
from iim

select * from iimx
Item Number              ONHAND
"E"18BLK                   .000
"E"18PPL                   .000
"E"18RED                   .000
"E"18WHITE                 .000
#1                       72.000
#2                       72.000
#231GUDEBR            1,412.000

DSPFD IIMX
And right in the description you'll see:
SQL view create statement . . . . . . . . . :
  CREATE VIEW IIMX AS SELECT IPROD, ROB.ONHAND(IOPB, IRCT, IADJ, IISS)
   AS ONHAND FROM DATDIVF.IIM

Now you can create your Queries, write RPG, etc against this file.  And if
you change your method of calculating ONHAND you only have to change that
one subprocedure and all your Queries, etc are fixed.


Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin