MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » September 2014

RE: testing an SQL procedure



fixed

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.





Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact