When using system naming conventions you cannot qualify UDF calls (at least
not before the last TR in release 7.1).
The / is interpreted as divide.
Beginning with the one of the last PTFs you can use / (slash) or . (period)
for qualifying objects when using system naming conventions.
If a UDF is qualified by using the period as separator it is possible the
UDF is executed even though system naming conventions are used.
If you are not yet on release 7.1 with the latest PTFs, you need to add the
library/schema in which the UDF is located to the library list.
Mit freundlichen Grüßen / Best regards
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"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!"
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary
Gesendet: Thursday, 04.9 2014 23:35
An: Midrange Systems Technical Discussion
Betreff: RE: testing an SQL procedure
Thanks all for your support, especially Chuck, Rob and Vern The current
'solution' uses an SQL Procedure and an SQL Function SQL Function GETLIBF is
a utility to identify the 'Division Library'
given an 'Outlet Location' value.
SQL Procedure FSP975DN will be used in a Month End process to create data
for GL entries and which is saved in local files for reporting and analysis.
The SQL Function:
CREATE FUNCTION MYLIB/GETLIBF (
out_loc CHAR(4) )
READS SQL DATA
DECLARE div_lib CHAR(10);
SELECT MAX(lclib) INTO div_lib
WHERE lcwhse = out_loc
AND lcbas IN ('S','V');
The SQL Procedure:
CREATE PROCEDURE MYLIB/SQLPROC1
(IN out_loc CHARACTER (4),
IN cto_num DECIMAL(13,0),
IN bat_dat DECIMAL(7,0),
OUT doc_num CHAR(11)
READS SQL DATA
DECLARE lib_nam VARCHAR(10) ;
DECLARE sql_str VARCHAR(256) ;
DECLARE C1 CURSOR FOR S1;
SET lib_nam = getlibf(out_loc);
SET sql_str = 'SELECT MAX(docnum) AS docnum FROM ' ||
TRIM(lib_nam) || '/tbla WHERE docnum LIKE ''__F%''' ||
'AND outloc = ? AND ctonum = ? AND batdat = ? ' ||
'AND traidf = ''4200''';
PREPARE S1 FROM sql_str;
OPEN C1 USING out_loc, cto_num, bat_dat;
FETCH C1 INTO doc_num;
One detail surprised me: I got 'SQL0206 Column or global variable MYILB not
If I qualified the SQL Function with library name: SET lib_nam =
Also, I was not able to run 'SET lib_nam = getlibf(out_loc);' from my
SQLRPGLE test pgm and get a value for lib_nam ?
SQLCOD was zero, but lib_nam was blanks ?
But, most everything about SQL Procedures and Functions has me in
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,
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l