Re: UDF not found when importing to Excel via Microsoft Query

I assume the *SRVPGM is in OELIB?

Is the UDF defined as a call to a procedure in XX0504V?

Can you show your query and the parms defined for procedure and UDF?

I suspect it is a parm mismatch. You might try using CHAR(), DEC(), or
CAST() to explicitly match the param types.

Charles

On Wed, Oct 12, 2016 at 9:45 AM, Bill Reed <breed@xxxxxxxxxxxxxx> wrote:

I have created a UDF to return a scalar function within an SQL Select
statement. The UDF references a procedure within a service program. The
Select works fine when using SQL on the IBM i - Run SQL Scripts from
Navigator, Run SQL Scripts from ACS (is that the same?), and green-screen
STRSQL. But when I try to reference it from within Excel, using "Get
external data" / "From other sources" / "From Microsoft Query", I get
"SQL0204 - XX0504V in OELIB type *SRVPGM not found" (where XX0504V is in
fact the name of the service program).

I have learned that the SQL0204 "not found" message can really mean
something like parameter mismatch, so I have checked and tinkered with
those to no avail. The connection defined in Excel has the same user ID
and the same library list, plus the UDF name is qualified; I see no reason
that it can't really be found. I suspect either a difference in syntax
between the two SQL's (MS and IBM i) or something else relating to the
connection definition. All other SQL which I have copied over in this way
works fine; it seems to be only the UDF.

(I'm new on this list so forgive me if this has been addressed before; I
was not able to find anything in the history.)

Thanks for any direction.

Bill Reed
--
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: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.