Re: SQL Functions in RPG

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]:

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzdclpef.htm]
IBM i 7.2-> Database-> Reference-> SQL reference-> Statements->
_DECLARE PROCEDURE_
"The DECLARE PROCEDURE statement defines an external procedure.

Invocation

This statement can only be embedded in an application program. It is not an executable statement. ...
..."


[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcallsta.htm]
IBM i 7.2-> Database-> Reference-> SQL reference-> Statements->
_CALL_

The CALL statement calls a procedure.
Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

...

procedure-name or variable

Identifies the procedure to call by the specified procedure-name or the procedure name contained in the variable. The identified procedure must exist at the current server.

...

If the procedure-name identifies a procedure that was defined by a DECLARE PROCEDURE statement, and the current server is a DB2® for i product, then:

• The DECLARE PROCEDURE statement determines the name of the external program, language, and calling convention.
• The attributes of the parameters of the procedure are defined by the DECLARE PROCEDURE statement.
..."