RE: testing an SQL procedure

Thanks - would not have guessed, but it makes sense - and I hope to
see 'Dynamic Compound Statement' work today - assuming Ops Mgr has
had ample time to keep to his ptf schedule . . .

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, September 04, 2014 11:12 PM
To: 'Midrange Systems Technical Discussion'
Subject: AW: testing an SQL procedure

Hi,

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

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: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
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) )
RETURNS CHAR(10)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA

BEGIN
DECLARE div_lib CHAR(10);
SELECT MAX(lclib) INTO div_lib
FROM alib/locf
WHERE lcwhse = out_loc
AND lcbas IN ('S','V');
RETURN div_lib;
END;

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)
)
LANGUAGE SQL
DETERMINISTIC
READS SQL DATA

P2: BEGIN
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;
CLOSE C1;
END P2

One detail surprised me: I got 'SQL0206 Column or global variable MYILB not found.'
If I qualified the SQL Function with library name: SET lib_nam =
mylib/getlibf(out_loc) ?
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 go-to-school mode.
--
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.


--
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.