Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
A lot of times I find issues with functions and what not if I don't set my schema (library on the iSeries). Also, did you add the schema for the function to your PATH or its equivalent on the iSeries? –  Chris Aldrich Oct 30 '13 at 20:17
    
What is 'my PATH'? Other than that, I am still in the same Navigator window I used to create the function. I can create and call a stored procedure in the same manner, but not a function. –  ajeh Oct 30 '13 at 20:20
    
Did you check the SQLSTATE to see what the documentation behind it list? Other thoughts I have are you do not have EXECUTE privelege on a PACKAGE (here is my LUW bent though vs the iSeries that WarrenT can speak better to). I know in LUW there is a PATH, which is what schemas the engine should search through to find the object in question. I think on the iSeries this is called the library list. –  Chris Aldrich Oct 30 '13 at 20:32
    
As a creator of this function, I should automatically gain privilege to call it. As a matter of fact, I have ALL privilege checked off, that was the first thing I verified. The permissions on this function are the same as on all that's there. –  ajeh Oct 30 '13 at 20:44
    
All functions, including the newly created one, have the same SET PATH... in generated SQL. –  ajeh Oct 30 '13 at 21:06

2 Answers 2

Start with this statement before trying to execute the function:

SET PATH TESTDAT;
share|improve this answer
    
Same error message. The only thing that's changed is that with SET PATH I can call my SPs without specifying schema. –  ajeh Oct 31 '13 at 15:31
    
What's the source for your UDTF? –  WarrenT Oct 31 '13 at 19:59
    
If you look in iNavigator, does it show the function is there, and defined as you expect? –  WarrenT Oct 31 '13 at 20:01
    
Yes to all, and not only in navigator. Like I said - I can see function information thru SquirrelSql, but cannot call it. I am updating full source of the function in the original question. –  ajeh Oct 31 '13 at 20:14

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

share|improve this answer
    
Are you suggesting it should work as this: SELECT * FROM TABLE(TESTDAT.FNREPORT(DATE('10/23/2013'), DATE('10/23/2013'), CHAR('ANY', 3))) AS T? –  ajeh Nov 18 '13 at 20:03
    
This is what I get back from that call: Error: [SQL0204] FNREPORT in TESTDAT type *N not found. SQLState: 42704 ErrorCode: -204 –  ajeh Nov 18 '13 at 20:09
    
I get exact same error message even if I completely remove the 3d parameter. –  ajeh Nov 18 '13 at 20:20
    
I updated my answer –  bluish Nov 19 '13 at 7:53
    
No parameters - same error. –  ajeh Nov 19 '13 at 16:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.