Re: SQL UDF to call RPG without service program -- RPG400-L

Still one of the best sources to learn about UDFs and UDTFs ...

-> https://www.scottklement.com/udtf/

Just dive into Scotts presentations and try it. Parameter style SQL oder DB2SQL is perfect - also when you want to create your own table functions.

What I do often is - I create a function in a service program - e.g.

mySpecialFunction

Now if I want to expose it to SQL, and I need some SQL-ish overhead (like checking for NULLs), I simply create a wrapper in the same service program:

mySpecialFunction_sql

Then I expose this function with a CREATE FUNCTION in SQL.

HTH
Daniel

Am 03.03.2026 um 15:45 schrieb Charles Wilt <charles.wilt@xxxxxxxxx>:

You can use a MAINLINE RPG program as an external scalar SQL UDF.
You simply need to use PARAMETER STYLE SQL. (note the 3rd line below,
emphasis mine)

All applicable parameters are passed. The parameters are defined to be in
the following order:
n parameters for the input parameters that are specified for the function.
****A parameter for the result of the function.****
n parameters for indicator variables for the input parameters.
A parameter for the indicator variable for the result.
A CHAR(5) output parameter for SQLSTATE. The SQLSTATE returned indicates
the success or failure of the function. The SQLSTATE returned can either be:
the SQLSTATE from the last SQL statement executed in the external program,
an SQLSTATE that is assigned by the external program.
The user may set the SQLSTATE to any valid value in the external program to
return an error or warning from the function.

A VARCHAR(517) input parameter for the fully qualified function name.
A VARCHAR(128) input parameter for the specific name.
A VARCHAR(1000) output parameter for the message text.
When control is returned to the invoking program, the message text can be
found in the 6th token of the SQLERRMC field of the SQLCA. Only a portion
of the message text is available. For information on the layout of the
message data in the SQLERRMC, see the replacement data descriptions for
message SQL0443 in message file QSQLMSG. The complete message text can be
retrieved using the GET DIAGNOSTICS statement. For more information, see
GET DIAGNOSTICS.

Zero to three optional parameters:
A structure (consisting of an INTEGER followed by a CHAR(n)) input and
output parameter for the scratchpad, if SCRATCHPAD was specified on the
CREATE FUNCTION statement.
An INTEGER input parameter for the call type, if FINAL CALL was specified
on the CREATE FUNCTION statement.
A structure for the dbinfo structure, if DBINFO was specified on the CREATE
FUNCTION statement.

Yes, the interface is more complex, but you gain a lot of control.
Personally, I've gotten to the point of always using PARAMETER STYLE SQL.

Of course, assuming an existing program called from other places, you could
add the additional parms as *OMIT and modify the code to see if the
additional parms where passed or not; thus allowing the program to
determine if it was called from RPG or SQL. When called from SQL, you can
do the extra work to the additional parms,

Charles

On Mon, Mar 2, 2026 at 4:43 PM Reeve <rfritchman@xxxxxxxxx> wrote:

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 <rogersonra@xxxxxxxxx>
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 <rfritchman@xxxxxxxxx> wrote:

External name is FUNC00001A: shouldn't you be calling that object?

On Mon, Mar 2, 2026 at 11:20 AM Eric Wesson <fjwesson@xxxxxxxxxxx>

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: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription

related

questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.