Re: SQL UDF to call RPG without service program

On Tue, Mar 3, 2026 at 9:15 AM Eric Wesson <[email protected]> wrote:

> 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;
>

Note that CALLED ON NULL INPUT means that the RPGLE program needs the
additional parms outline in my posts and needs to properly check the input
null indicator parms to see if they are NULL.

RETURNS NULL ON NULL INPUT is a better choice if you want to ignore the
additional parms used by PARAMETER STYLE SQL.

HTH,
Charles
-- 
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.