|
Ok, you got me. When you're ready, the concept you'll need to learn is "binder language". Frankly it's so simple it's astonishing. I went to 3 sessions of COMMON before it finally sunk in that it really was that simple. I think Jon was getting pretty frustrated with me by the end. Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com fkany@xxxxxxxxxxxxxxxxxx Sent by: midrange-l-bounces@xxxxxxxxxxxx 04/15/2005 08:14 AM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc Subject Re: SQL - *SRVPGM not found Rob wrote: "Heck, you still use EXPORT(*ALL)." Rob, I'm just using the example provided by the individual below ;-). Frank ======================================================= Rob Berendt supplied an example using a service program: 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? 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@xxxxxxxxx@midrange.com on 04/14/2005 06:03:11 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> Sent by: midrange-l-bounces@xxxxxxxxxxxx To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc: Subject: Re: SQL - *SRVPGM not found Actually, it doesn't even need the *MODULE. You can blast that as soon as you do the CRTSRVPGM if you want. I never move a *MODULE to a production machine. You'll figure this out in time. I can see you're rather new to ILE. Heck, you still use EXPORT(*ALL). Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com fkany@xxxxxxxxxxxxxxxxxx Sent by: midrange-l-bounces@xxxxxxxxxxxx 04/14/2005 04:38 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc Subject Re: SQL - *SRVPGM not found Rob, That was it. It works now. Before I left yesterday afternoon, I tried CRTBNDRPG using option 14 and I got an error on the compile. Here's the error: =============================================================== *RNF1514 A procedure cannot be exported when the external name is the same as the main procedure. =============================================================== I guess the SQL statement doesn't need the *PGM object to exist. Only the *SRVPGM and *MODULE objects. Frank rob@xxxxxxxxx@midrange.com on 04/14/2005 05:10:56 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> Sent by: midrange-l-bounces+fkany=averittexpress.com@xxxxxxxxxxxx To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> cc: Subject: Re: SQL - *SRVPGM not found The name of your subprocedure is FR47LATEP D FR47LATEP PR 1 0 But you are trying to call a subprocedure FR47LATE. external name 'AVRTWRK/FR47LATE(FR47LATE)' Rob Berendt -- Group Dekko Services, LLC Dept 01.073 PO Box 2000 Dock 108 6928N 400E Kendallville, IN 46755 http://www.dekko.com fkany@xxxxxxxxxxxxxxxxxx Sent by: midrange-l-bounces@xxxxxxxxxxxx 04/14/2005 03:16 PM Please respond to Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> To MIDRANGE-L@xxxxxxxxxxxx cc Subject SQL - *SRVPGM not found Can anyone see why I'm having the problem below? This worked yesterday. I'm doing something wrong or out of step that I did correctly yesterday. I keep getting an error in STRSQL when I try to use a UDF in my SQL statement: =================================================================== "FR47LATE in AVRTWRK type *SRVPGM not found." Here are the steps I've taken: ========================= 1) CRTRPGMOD MODULE(AVRTWRK/FR47LATE) SRCFILE(AVRTWRK/QRPGLESRC) 2) CRTSRVPGM SRVPGM(AVRTWRK/FR47LATE) EXPORT(*ALL) 3) CREATE FUNCTION AVRTWRK/FR47LATE (DEC (3,0), DEC (7,0), DEC (8,0),DEC (8,0), DEC (8,0), CHAR (7), DEC (3,0), CHAR (7), CHAR (20), CHAR (2), CHAR (6)) RETURNS DEC (1,0) language rpgle deterministic no sql returns null on null input no exterNal action allow parallel simple call external name 'AVRTWRK/FR47LATE(FR47LATE)' 4) DECLARE GLOBAL TEMPORARY TABLE FR47P457A AS (SELECT FR47LATE(FHOT, FHPRO, FHPUDT, FHDADT, FHDDAT, FHSCD, FHDT, FHCCD, FHCCT, FHCST, FHCZIP) AS LATE, FHOT, FHPRO, FHDT, FHDDAT, FHDTIM, FHDADT, FHPUDT, FHSCD, FHCCD, FHCCT, FHCZIP FROM FRL00144 WHERE FHDDAT >= 20040501 AND FHDDAT <= 20040531 AND FHOT = 004 AND FHDT = 003) WITH DATA Here is the error I get after I press enter for the DECLARE statement: ========================================================= FR47LATE in AVRTWRK type *SRVPGM not found. My library list has AVRTWRK at the top. WRKOBJ FR47LATE returns the following: ====================================================================== FR47LATE *SRVPGM AVRTWRK RPGLE FR47LATE *MODULE AVRTWRK RPGLE Return 1 if FB is Late -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. -- This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
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.