Re: SQL UDF to call RPG without service program

[Charles Wilt <charles.wilt@xxxxxxxxx>]

On Tue, Mar 3, 2026 at 9:15 AM Eric Wesson <fjwesson@xxxxxxxxxxx> wrote:

> This works like a charm. thank you!
> ________________________________
> From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Robert
> Rogerson <rogersonra@xxxxxxxxx>
> Sent: Monday, March 2, 2026 2:27 PM
> To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> 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