Why cannot I call a table function in iSeries DB2 that I just created?

Question

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.

Answer 1

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 a varchar. The database engine looks for an overloaded version of the function with varchar parameters, but doesn't find it.

Solution is either

• changing function parameter to varchar

• or cast parameter to char in the function call:

  SELECT * 
  FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), 
                              DATE('10/23/2013'), 
                              CAST('ANY' AS CHAR(3))
            )) AS T
  

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

Answer 2

Start with this statement before trying to execute the function:

SET PATH TESTDAT;