× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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




As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.