Re: SQL UDF...why do i need to qualify the UDF and the table being used?

[Michael Schutte <mschutte369@xxxxxxxxx>]

I always run sql in *SYS naming convention.   Then I can create the
function and run select statements without qualifications.  However, if you
plan on using *SQL I believe you may want to look at SET PATH(?) before
creating the UDF.



On Thu, Apr 25, 2013 at 3:14 PM, Needles,Stephen J <
SNEEDLES@xxxxxxxxxxxxxxxx> wrote:

> I'm new to UDF's.  I've found examples on creating and using external
> UDF's but this example uses just SQL.
>
> My function is defined as:
>
> CREATE FUNCTION library.MaxSEQupv
> (parm1 char(10),
>  parm2 dec(8,0))
> RETURNS dec(3,0)
> LANGUAGE SQL
> BEGIN
> RETURN(select
> cast(max(field3) as decimal(3,0))
> from library.table
> where field1 =
> parm1 and field2 <= parm2);
> END
>
> In order to get it to work, I needed to qualify the table.  I would prefer
> to make this schema independent.
>
> I tested using:
>
> select library.MAXSEQUPV(cast('3434343434' as char(10)),
> cast(20120301 as decimal(8,0)))
> from sysibm.sysdummy1
>
> again...in order to get it to work, I had to change over to *SQL rather
> than *SYS for naming conventions and had to qualify the UDF.  Again, I
> would prefer to make the UDF schema independent.
>
> What have I done wrong?
>
> Steve Needles
> Northland Insurance - A Travelers Company
> 385 Washington Street, SB03N
> St. Paul, MN 55102
> Tel: 651-310-4203
> sneedles@xxxxxxxxxxxxx<mailto:sneedles@xxxxxxxxxxxxx>
>
> ________________________________
> This communication, including attachments, is confidential, may be subject
> to legal privileges, and is intended for the sole use of the addressee. Any
> use, duplication, disclosure or dissemination of this communication, other
> than by the addressee, is prohibited. If you have received this
> communication in error, please notify the sender immediately and delete or
> destroy this communication and all copies.
>
> TRVDiscDefault::1201
> --
> 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.