AW: User Defined Function Not Found In Library List

Another guess: if Run SQL scripts does not find your *SRVPGM, maybe try

Setting "Default SQL schema" in Run SQL Scripts:
You mean if SQL naming conventions are used?
... but only unqualified specified tables, views and indexes are searched
within the Default or Current Schema.
Unqualified specified Stored Procedures and UDFs are searched within the SQL
Path and when setting the SQL Path the special value *LIBL is allowed.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary
Thompson
Gesendet: Thursday, 04.12 2014 17:14
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: User Defined Function Not Found In Library List

Another guess: if Run SQL scripts does not find your *SRVPGM, maybe try
Setting "Default SQL schema" in Run SQL Scripts:
click Connection on the Menu bar,
Select JDBC Settings,
look at the first option on the System Tab


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
jhaskins@xxxxxxxxxxxx
Sent: Thursday, December 04, 2014 9:07 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: RE: User Defined Function Not Found In Library List

Ok, here is the PI
___________________________________________
P CheckFreightPolicy...
P B EXPORT
D CheckFreightPolicy...
D PI 1A
D orderNumber 6 0
D shippingWarehouse...
D 4A

Here is the UDF definition (I change the last parameter to varchar to
avoiding needing to cast it in SQL.)
___________________________________________________________
create function v92files75/CheckFreightPolicy
(orderNumber decimal(6, 0), shippingWarehouse
varchar(4))
returns char(1)
language rpgle
deterministic
no sql
external name 'V92MODS/FRGTPLCY (CheckFreightPolicy)'
parameter style general
program type sub

Here is the SQL statement that I've been using in Run SQL Scripts.
__________________________________________

select CHECKFREIGHTPOLICY (213177, 'W1 ') from sysibm/sysdummy1;

_______________________________

It's now finding the function, but not an object of type *SRVPGM in the
library with the name above, although it's there if you do a WRKOBJ.

Janell Haskins,
Senior Business Analyst &
Systems Manager

Knit-Rite, Inc.
120 Osage Ave.
Kansas City, KS 66105

(913)279-6342


-----Original Message-----
From: Charles Wilt [mailto:charles.wilt@xxxxxxxxx]
Sent: Thursday, December 04, 2014 9:58 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: User Defined Function Not Found In Library List

Post the PI, the UDF definition and the test statement.

Charles

On Thu, Dec 4, 2014 at 10:53 AM, <jhaskins@xxxxxxxxxxxx> wrote:

I've tried running it from both STRSQL and Run SQL Scripts, I get the
same message.

The function was created in the same library as our production data
files. The service program that the function executes is in a
different library, though. From what I can tell, the program library
isn't in the library list for the job the Run SQL Scripts runs under.
I tried creating the service program in the production data library
and it didn't correct the problem.

Janell Haskins,
Senior Business Analyst &
Systems Manager

Knit-Rite, Inc.
120 Osage Ave.
Kansas City, KS 66105

(913)279-6342
-----Original Message-----
From: Gary Thompson [mailto:gthompson@xxxxxxxxxxx]
Sent: Thursday, December 04, 2014 8:47 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: User Defined Function Not Found In Library List

How are you running your UDF; STRSQL, Run SQL Scripts, Debug session ?
First guess is the library list is not what you assume it to be . . .

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
jhaskins@xxxxxxxxxxxx
Sent: Thursday, December 04, 2014 7:33 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: User Defined Function Not Found In Library List

Good morning;

I'm attempting to create my first SQL user defined function (UDF)
based on a service program written in RPGLE.

The function was created without any errors and I can see it when I
look at the functions for the schema (database) that I created it in.
However when I try to test it in a SQL select statement I get a
message saying that it isn't found in the library list. Since it's in
the same library as the files in the schema, I can't understand why it

isn't being seen.

Obviously, since this is my first time, I've done something wrong
and/or missed a step. Can you offer any advice or suggestions about
what is going on and how to correct the problem?

Thanks!


Janell Haskins,
Senior Business Analyst &

Systems Manager

Knit-Rite, Inc.
120 Osage Ave.
Kansas City, KS 66105


(913)279-6342

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.