× 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.


  1.  Home
  2. MIDRANGE-L
  3. April 2005

Re: SQL - *SRVPGM not found

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 thread ...
Replies:
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.