Re: SQL UDF to call RPG without service program

Rob, "regular" RPG programs (with a mainline, I guess) can't return a
value.  A function implicitly returns a single value.  Service programs
(ctl-opt *NOMAIN) can return a value.

Two ideas:
1) Read up on parameter passing and overloading.  If the attributes of the
caller's parameter list don't fit with the called parameter list, you get a
Not Found; I think a returned value is not considered in signature matching.
2) Set up a dummy service program named GETUPCHARGEFUNC and see if your
code works with it.  If so, your SQL is good.

--reeve

On Mon, Mar 2, 2026 at 12:28 PM Robert Rogerson <[email protected]>
wrote:

> Hi Reeve,
>
> I may be missing something but why not just call the RPG from the
> function?  I'm not sure of the purpose of the procedure and if it's needed.
>
> Here's an example of a function calling an RPG program.
>
> CREATE FUNCTION MYLIB.GETUPCHARGEFUNC (
>     WHSENBR NUMERIC(3, 0) ,
>     ITEMNBR NUMERIC(6, 0) )
>     RETURNS NUMERIC(7, 2)
>     LANGUAGE RPGLE
>     SPECIFIC MYLIB.GETUPCHARGEFUNC
>     NOT DETERMINISTIC
>     MODIFIES SQL DATA
>     CALLED ON NULL INPUT
>     NOT FENCED
>     EXTERNAL NAME 'MYLIB/GETUPCHRGF'
>     PARAMETER STYLE SQL ;
>
> To call
> SELECT
>        ivwhid AS "Warehouse",
>        ivitm# AS "Item Number",
>        GETUPCHARGEFUNC(ivwhid, ivitm#) AS "Up Charge"
>   FROM invmasp
>  WHERE ivwhid = 90
>    AND ivitm# = 5
>  ORDER BY 1, 2;
>
> HTH,
>
> Rob
>
> On Mon, Mar 2, 2026 at 2:43 PM Reeve <[email protected]> wrote:
>
> > External name is FUNC00001A: shouldn't you be calling that object?
> >
> > On Mon, Mar 2, 2026 at 11:20 AM Eric Wesson <[email protected]>
> wrote:
> >
> > > I am trying to create an SQL function that effectively calls an RPG
> > > program and returns the result. I've read you can do it by creating a
> > > service program and calling it but I'm trying to do it without the
> > service
> > > program.
> > >
> > > I have successfully created an sql stored procedure that calls the RPG
> > > program and I've created a function that calls the stored procedure.
> > >
> > > When i try to use the function, I get an error saying "FUN0001 not
> > found".
> > > I can successfully call the function directly using "CALL
> > > APPSTRPROC.FUNC0001(12345, 0)".
> > > I've beat my head against the wall on this one. Any ideas?
> > >
> > >
> > > Code that creates procedure and function
> > > // Stored procedure to call rpg
> > >          Exec sql
> > >            Create or replace Procedure Appstrproc.FUNC0001(
> > >                                          in SystemSku dec(15),
> > >                                          out Retail dec(9,2))
> > >            Language RPGLE
> > >            Called on null input
> > >            Not deterministic
> > >            No external action
> > >            Reads sql data
> > >            External name FUNC00001A
> > >            Parameter style general;
> > >
> > >          // Function to call stored procedure
> > >          Exec Sql
> > >            Create or Replace Function
> > > Appstrproc.f_SkuGetCurrentRetail(SystemSku dec(15))
> > >            Returns Decimal(9,2)
> > >            Language SQL
> > >            Return Appstrproc.FUNC0001(SystemSku, 0);
> > >
> > > Thanks,
> > > Eric
> > > --
> > > This is the RPG programming on IBM i (RPG400-L) mailing list
> > > To post a message email: [email protected]
> > > To subscribe, unsubscribe, or change list options,
> > > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > > or email: [email protected]
> > > Before posting, please take a moment to review the archives
> > > at https://archive.midrange.com/rpg400-l.
> > >
> > > Please contact [email protected] for any subscription
> related
> > > questions.
> > >
> > >
> > --
> > This is the RPG programming on IBM i (RPG400-L) mailing list
> > To post a message email: [email protected]
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> > or email: [email protected]
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/rpg400-l.
> >
> > Please contact [email protected] for any subscription related
> > questions.
> >
> >
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact [email protected] for any subscription related
> questions.
>
>
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.