Re: SQL Functions in RPG

On 02-Nov-2015 14:18 -0600, CRPence wrote:

On 02-Nov-2015 12:57 -0600, Englander, Douglas wrote:

Does anyone know if I can create a subprocedure in an ILE RPG
program, and use embedded SQL in that program to reference that
subprocedure as a SQL function, instead of creating a SQL function
that points to a subprocedure located in a Service Program? I am
looking for something like a SQL "temporary function" that would
be used only by that program, and only when that program is active.
If so, can you point to some examples?

Perhaps use a PROCEDURE instead? So instead of defining a RETURNS
for a return-value or return-table on a CREATE FUNCTION, define a
parameter of the procedure [and the corresponding parameter on a SQL
routine definition] for returning a scalar result, or define a
RESULT SET for returning a result-table, and then use the DECLARE
PROCEDURE vs the CREATE PROCEDURE to define that routine interface to
the SQL [for use in the SQL CALL rather than invoked as a scalar
expression or as a table expression] <<SNIP>>

I do not see that an exported procedure from a *PGM is visible to the EXTERNAL NAME specification, so that idea seems not to be an option.

Like Mike suggests in a followup reply to the OP, describing why and more details about what needs to be achieved, might enable redirection in replies, to a more fruitful path to follow than what is described in the OP as being pursued.