Re: SQL UDF to call RPG without service program

This works like a charm. thank you!
________________________________
From: RPG400-L <[email protected]> on behalf of Robert Rogerson <[email protected]>
Sent: Monday, March 2, 2026 2:27 PM
To: RPG programming on IBM i <[email protected]>
Subject: Re: SQL UDF to call RPG without service program

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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970191086%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=1jmWPyo1HwYyEFMQIpGthNwyP9RjgNWn8jDQFCqPB%2BU%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l>
> > or email: [email protected]
> > Before posting, please take a moment to review the archives
> > at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970224791%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=l8YoymiuPPUuPwac0bEB3CtdIdG9DeaGvhPOlQIfpko%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970257491%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=NRNvWUTpCh4nR8D1D%2Bsq8nGTECAUkFj5DpCqxBIonVA%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l>
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970286542%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Xa%2F0zur3igrzsjKLlWGVuYR07UZYlFoNAJT1Fi2r8bE%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970475796%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=wxZgkAm9kRairJDlmZYSQV5WWoRwHMowFaNGgTkXpgg%3D&reserved=0<https://lists.midrange.com/mailman/listinfo/rpg400-l>
or email: [email protected]
Before posting, please take a moment to review the archives
at https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Frpg400-l&data=05%7C02%7C%7Ccd4d588636954656823308de789a37e6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C639080800970510204%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=vcQckuHIp3KeOiMy%2ByGBUXB%2BdrUIb91qSuAGQXMs2s8%3D&reserved=0<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.