On 19-Oct-2015 12:19 -0500, Mario Martoriello wrote:
On 19-Oct-2015 11:42 -0500, CRPence wrote:
On 19-Oct-2015 10:56 -0500, Mario Martoriello wrote:
I wrote, reading an old Scott Klement ‘s article, my own UDTF.
This UDTF would return me a table. To define the UDTF I used
the following code:
Create Function RtvFattxCl
( cdcli Char(6)
, DatIni Decimal(8, 0)
, DatFin Decimal(8, 0)
)
<<SNIP>>
external name 'MC_PTF/XVEX850(RTVFATTXCL)'
program type sub
language rpgle
<<SNIP>>
<<SNIP>> So the given example invocation should be modified for
testing, to do something like the following [where a1, a2, and a3
would be variables with data types either matching or compatible
types with the declared parameters, or with constant\literal
values which yield the desired type:
select CC.* from TABLE(rtvfattxcl(a1, a2, a3)) AS CC
Note: Per the choice of CHAR() instead of VARCHAR() for the
parameter declaration of CDCLI, the first argument specified as a
[apostrophe-delimited] string constant will not resolve to that
function, because the implicit typing for a character string
constant is to a VARCHAR. Generally best when possible, to define
the function [and code the matching procedure] to use varying
character instead. <<SNIP>>
<<SNIP>>
I wrote an wrong example because the exact call that I did is:
select * from table(RTVFATTXCL('306803', 20120101)) as temp
The third parameter is optional and in the function I tried to
handle the null field.
That the third parameter is optional to the external procedure is
irrelevant in the given scenario, at least from the perspective of the
SQL. The parameter would need be defined as optional to the SQL, in
order that the SQL interface should be accepting of the Function
invocation without the third argument being specified. That is because
the CREATE FUNCTION is defining the interface to the SQL for both the
program from the SQL to the ROUTINE [in this case FUNCTION] from the
perspective of the SQL programmer.
In the DB2 for i SQL vernacular, the _optional arguments_ are defined
by the parameters that are declared with a DEFAULT value; i.e. they are
not explicitly referred to as coding for Optional arguments or defining
Optional parameters. And AFaIK the capability is new to IBM i 7.2 [V7R2].
So revisiting the originally offered routine [as table-function]
source definition, the following revision to that statement is required
to /make the 3rd parameter optional/; in this updated example, asking
the SQL to pass the NULL value [indicator] in place of a value for the
third argument, whenever the FUNCTION() invocation does not include the
third parameter:
Create Function RtvFattxCl
( cdcli VarChar(6) /* use VARCHAR to alw tst w/ literals */
, DatIni Decimal(8, 0)
, DatFin Decimal(8, 0) DEFAULT NULL /* added DEFAULT *?
) [...]
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/sqlp/rbafyudfdft.htm]
_Defining UDFs with default parameters_
"You can define parameters for UDFs to have default values.
A default value for a parameter means that the parameter is not required
on the function invocation. Using defaults allows flexibility by not
requiring every invocation of a function to pass all parameter values.
This can be useful when modifying existing functions.
..."
With those two revisions, then on v7r2, the following invocation
should be supported [given system-naming OPTION is established, and the
PATH is *LIBL, and the routine RTVFATTXCL is defined in a SCHEMA name
that exists in the *LIBL of the job invoking that FUNCTION]:
select temp.* from table(RTVFATTXCL('306803', 20120101)) as temp
I tried also specifying the lib, alias schema, in which I registered
my function always with the same result (SQL0204)
Fix the problems with the parameter definitions [and\or the argument
specifications], and that issue will presumably be resolved; i.e. the
function resolution is apparently failing not because of library [list]
problems, but because of parameter\argument compatibility [and omission]
problems.
About the first parameter that I defined Char() instead of varchar I
don't understand what you mean about apostrophe-delimited variable
To be clear, "the first argument specified as a
[apostrophe-delimited] string constant". In the "exact call", the first
argument is a "string constant"; in the SQL, a "string constant" is
denoted by apostrophe-delimited text. That string constant, specified
as '306803' for the first argument in the "exact call", is data-typed by
the SQL as being VARCHAR(6); thus when the SQL goes looking for a
table-function with a compatible first-parameter, the SQL "Function
Resolution" will look *only* for a function by the specified name with a
VARCHAR() declared as the first parameter.
however I can change it to varchar .
That will assist in testing, if using string literals [aka string
constants] is desirable. That is because the string-literal will be
interpreted by the SQL as being VARCHAR, and thus the corresponding
parameter type-definition [1st argument for the first parameter] will be
compatible... and thus the Function Resolution can find a matching
routine [aka Function] because both the function-name and the parameters
will match; well, at least the first of the them will match with just
the VARCHAR change, but unless a compatible argument is specific for all
three parameters, then until the DEFAULT change is also effected for the
third parameter definition, that 3rd argument can not be omitted.
midrange.com
