Re: creating table function - UDTF and then calling

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