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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.