Using iSeries Navigator 'Run an sql script' window I created a new table function.
When I go to the Functions branch under the schema where it was created, I see the function among the other few. But when I call the function in the same Run an sql script window, I get an error:
SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), 'ANY')) AS T
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] FNREPORT in TESTDAT type *N not found.
Using SquirrelSql client I can see the function in the schema as well, but cannot call it due to the same error.
I can call other functions in the schema, but not the one I just created. What can be the reason?
I can call Generate SQL, copy/paste the schema and function name into query window, and then it will throw the above error.
Update:
Full source code of the function
CREATE FUNCTION TESTDAT.FNREPORT (
DATESTART DATE ,
DATEEND DATE ,
ICLASS CHAR(3) )
RETURNS TABLE (
A INTEGER )
LANGUAGE SQL
SPECIFIC TESTDAT.FNCMSREPORT
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
DISALLOW PARALLEL
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN ATOMIC
RETURN
SELECT 1 AS A FROM SYSIBM . SYSDUMMY1 ;
END ;
Just to clarify that the solutions suggested below did not work.
If the query above was changed to
SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), CAST('ANY' AS CHAR()3)) AS T
or parameter ICLASS removed, or all parameters removed from the function definition, the exact same error was reported.
Best Answer
As Heinz Z. did, I discovered the problem.
One of your function parameters is
char
, while you pass the string literal'ANY'
, which is considered avarchar
. The database engine looks for an overloaded version of the function withvarchar
parameters, but doesn't find it.Solution is either
varchar
or cast parameter to
char
in the function call:If doesn't work, try in any case to remove all parameters from the function and see if they are the culprits. Then you can investigate deeper adding one by one, try to work on dates format, for example you can try to pass
current date
instead of 10/23/2013.Also you must investigate on why it thinks that
FNREPORT
is a type and not a function...