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)
)
Returns Table
( NCDCLI char(6),
NCDRGI char(2),
NTFTIP char(1),
NAADOC decimal(4, 0),
NTFNRO decimal(7, 0),
NTFDAT date,
NTFAGE char(3),
TTNetM decimal(11, 2),
TTTras decimal(11, 2),
PercTr decimal(5, 2)
)
external name 'MC_PTF/XVEX850(RTVFATTXCL)'
program type sub
language rpgle
parameter style db2sql
no sql
not deterministic
external action
disallow parallel
It seems that everything was ok. So if I do a Select on the system
catalog SYSFUNCS I see my function and its name RTVFATTXCL, but when
I use it with SQL like
select * from rtvfattxcl
I got an SQL0204 error message that tell me RTVFATTXCL in *LIBL type
*N not found. Can anyone tell me which is my mistake? I am on a 6.1
box.
The apparent /table-reference/ in that SELECT is not coded correctly
to invoke a /TABLE-function/; the SQL is looking for a /file/ [type
*FILE, despite the *N being displayed in the message].
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafztabref.htm]
table-reference
A table-reference specifies an intermediate result table.
>>-+-single-table----------------+-----------------------><
+-nested-table-expression-----+
+-table-function--------------+
...
...
table-function
|--TABLE--(--function-invocation--)--correlation-clause---|
...
.-AS-.
|--+----+--correlation-name--+-----------------------+----|
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
..."
As well, the UDTF was defined with three arguments, none of which
were specified; nor are the require parentheses included, both to denote
the function vs name of a table, view, or whatever name would be allowed
in that context, and serves as the enclosure for the specification of
any arguments. Notably, the function is located according to the
specifications.
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/db2/rbafzfuncres.htm]
_Function resolution_
"A function is invoked by its function name, which is implicitly or
explicitly qualified with a schema name, followed by parentheses that
enclose the arguments to the function.
Within the database, each function is uniquely identified by its
function signature, which is its schema name, function name, the number
of parameters, and the data types of the parameters. Thus, a schema can
contain several functions that have the same name but each of which have
a different number of parameters, or parameters with different data
types. Or, a function with the same name, number of parameters, and
types of parameters can exist in multiple schemas. When any function is
invoked, the database manager must determine which function to execute.
This process is called function resolution.
..."
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.
Here is an example showing a [one-argument] UDTF being invoked using
a Host Variable:
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/sqlp/rbafyudftableex.htm]
_Example: SQL table UDFs_
"...
SELECT * FROM TABLE(PROJFUNC(:datehv)) X
..."
midrange.com
