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.
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.