RE: Controlling Library List in User Defined Functions

Looks like when you create a UDF in a job using *SQL naming convention it
resolves to the table using the current schema... I must admit, I don't
understand why it does that.

Try recreating your UDF while in a job using *SYS naming convention. For
example:

1) DROP FUNCTION GBCMODS/RTVNRACCTCLASS
2)
CREATE FUNCTION GBCMODS/RTVNRACCTCLASS (
INOBJ CHAR(6) ,
INSUB CHAR(8) )
RETURNS CHAR(3)
LANGUAGE SQL
SPECIFIC GBCMODS/RTVNRACCTCLASS
NOT DETERMINISTIC
READS SQL DATA
CALLED ON NULL INPUT
NO EXTERNAL ACTION
NOT FENCED
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *OPTIMIZE ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
BEGIN

DECLARE RECCOUNT INT ;
DECLARE CLASS CHAR ( 3 ) NOT NULL DEFAULT ' ' ;
SET PATH = GBCMODS , GBCCOM ;

IF SUBSTR ( INSUB , 4 , 5 ) = '93720' OR
INSUB >= '92000000' AND INSUB <= '92099999' AND INOBJ = '4215 '
OR
INSUB >= '99000000' AND INSUB <= '99099999' AND INOBJ = '4215 '
THEN
SET CLASS = 'BON' ;
ELSE
SELECT COUNT ( * ) INTO RECCOUNT FROM F0005 WHERE DRSY = '56 ' AND DRRT =
'JS' AND TRIM ( DRKY ) = SUBSTR ( INSUB , 4 , 5 ) ;

IF RECCOUNT > 0 THEN
SET CLASS = 'MIS' ;
ELSE
SELECT COUNT ( * ) INTO RECCOUNT FROM F0005 WHERE DRSY = '56 ' AND DRRT
= 'JO' AND TRIM ( DRKY ) = TRIM ( INOBJ ) ;

IF RECCOUNT > 0 THEN
SET CLASS = 'PAY' ;
ELSE
SET CLASS = 'OTH' ;
END IF ;
END IF ;
END IF ;

RETURN CLASS ;

END ;


Btw, I don't think you have to pad your character literals with blanks (like
'56 '; '56' ought to work fine).

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: Controlling Library List in User Defined Functions

F0005 is definitely in my library list, though not in library SCOYLE.
I'm not sure why it is only searching for the file in SCOYLE. I don't
want to have to run any commands before invoking the function. I'd like
it to just use the *LIBL of whatever environment it happens to be
running in.

Thanks....I will read up on your link...
- Steve