Hi
thank you Birgitta and Chuck now it works :)
best regards
Mario
-----Messaggio originale-----
From: Birgitta Hauser
Sent: Monday, October 19, 2015 11:33 PM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
Subject: AW: Problem calling an RPG UDTF
Hi Mario,
select * from table(RTVFATTXCL('306803', 20120101)) as temp
And here is exactly the problem: your UDTF is defined with 3 Input
parameters, that are defined as CHAR and DECIMAL (=Packed with zero Decimal
positions).
You call the UDTF with 2 Parameters (only) and these parameters are passed
with Data Type VARCHAR and INT which are both different from the originally
defined parametes.
Because UD(T)Fs can be overloaded, i.e. an UDTF with the same name but
different parameter definition can exist multiple times within the same
library, and there is apparently no UDTF with only 2 parameters with an
VARCHAR type and an Integer type parameter, the UDTF is not found.
Call the function with the right number of parameters an explicitly cast
your parameters into the expected data types.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Mario
Martoriello
Gesendet: Monday, 19.10 2015 19:19
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: Problem calling an RPG UDTF
Hello Chuck
Thank you for your answer. 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.
I tried also specifying the lib, alias schema, in which I registered my
function always with the same result (SQL0204) About the first parameter
that I defined Char() instead of varchar I don't understand what you mean
about apostrophe-delimited variable however I can change it to varchar .
Best regards
Mario
-----Messaggio originale-----
From: CRPence
Sent: Monday, October 19, 2015 6:42 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: Problem calling an RPG UDTF
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/rba
fztabref.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/rba
fzfuncres.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/rb
afyudftableex.htm]
_Example: SQL table UDFs_
"...
SELECT * FROM TABLE(PROJFUNC(:datehv)) X
..."
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
midrange.com
