Re: creating table function - UDTF and then calling

[Jim Franz <franz9000@xxxxxxxxx>]

same error , but this is deeper in the message text   SQLSTATE 42704 , also
reason code 1 from CPF503E
Cause . . . . . :   An error occurred while invoking user-defined function
LOCATIONS_BY_FUND_MEMBER in library KRMDATA. The error occurred while
invoking the associated external program or service program LOCAT00001 in
library KRMDATA, program entry point or external name
LOCATIONS_BY_FUND_MEMBER_1, specific name LOCATIONS_BY_FUND_MEMBER. The
error occurred on member QSQPTABL file QSQPTABL in library QSYS2. The error
code is 1. The error codes and their meanings follow:
          1 -- The external program or service program returned SQLSTATE
42704. The text message returned from the program is:
LOCATIONS_BY_FUND_MEMBER in QSYS type *LIB not found. .

when I look at function in systables
SPECIFIC_NAME                                    SPECIFIC_SCHEMA
EXTERNAL_NAME

LOCATIONS_BY_FUND_MEMBER        KRMDATA
KRMDATA/LOCAT00001(LOCATIONS_BY_FUND_MEMBER_1)
********  End of data  ********
Jim

On Thu, Jan 25, 2018 at 9:48 AM, Rob Berendt <rob@xxxxxxxxx> wrote:

> Just for giggles try
> select * from TABLE(KRMDATA.LOCATIONS_BY_FUND_MEMBER(dec(730, 3, 0),
> dec(87, 5, 0))) ASC
>
> Perhaps it's "overloading"?
>
>
> Rob Berendt
> --
> IBM Certified System Administrator - IBM i 6.1
> Group Dekko
> Dept 1600
> Mail to:  2505 Dekko Drive
>           Garrett, IN 46738
> Ship to:  Dock 108
>           6928N 400E
>           Kendallville, IN 46755
> http://www.dekko.com
>
>
>
>
>
> From:   Jim Franz <franz9000@xxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:   01/25/2018 09:43 AM
> Subject:        creating table function - UDTF and then calling
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> one of our java developers (on a non-I system) wants to create a table
> function to call  - it creates but does not execute and the messaging is
> confusing (to me)
>
> Create statement (partial - it's big
>
> CREATE OR REPLACE FUNCTION KRMDATA.LOCATIONS_BY_FUND_MEMBER(EMFND#
> DECIMAL(3,0), EMMEMB DECIMAL(5,0))
>
>   RETURNS TABLE (EMPSPL CHAR(30), EMFDYR DECIMAL(4,0), EMFND#
> DECIMAL(3,0),
> EMMEMB DECIMAL(5,0), EMMSUB DECIMAL(2,0), EMNAME CHAR(30), EMFDID
> DECIMAL(9,0), LOCATION_# CHAR(30)
>
>   LANGUAGE SQL
>
>   READS SQL DATA
>
>   NO EXTERNAL ACTION
>
>   DETERMINISTIC
>
>   RETURN
>
>    SELECT EM.EMPSPL ,EM.EMFDYR ,EM.EMFND# .... much more
>
>
> calling from V7R1 i Navigator
> calling - select * from TABLE(KRMDATA.LOCATIONS_BY_FUND_MEMBER(730,87))
> ASC
>
> (the ASC - syntax checker was requiring "something " there
>
>
> when calling , get 4 message in log
>
> SQL0204  LOCATIONS_BY_FUND_MEMBER in QSYS type *lib not found
> Diagnostic
>
> CPF503E  user defined function error on member
> QSQPTABL                           Notify
>
> CPF503E  user defined function error on member
> QSQPTABL                           Senders Copy
>
> SQL0204  LOCATIONS_BY_FUND_MEMBER in QSYS type *lib not found
> Diagnostic
>
>
> even though it says it's not found, it found it and returned an empty
> table
> (with column headings)
>
> Is there an issue with SQL only table function?
> Is this a 10 character limit on function name?
> Something else?
>
> Jim Franz
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD