hi all,

I have a problem in executing stored procedure in as400. I could able to compile and run in linx v8.1 environment. I could be able to compile in as400 but i could not able to view the output in as400 v5r1.

I have added the text below where the select query selected rows from the client centre , and if the select stmt. is included in the stored procedure shows zero records.

I have compiled and executed the as400 stored procedure from the client(linux db2v8.1) which is actually connected to as400 v5r1.
----------------------------------------------------------------------------------

db2 -td@ -vf proc_mesa
CREATE PROCEDURE ecapsebbs.relmaintsl(in datecriteria date)
language sql
BEGIN
DECLARE v_stmt VARCHAR (2500);
DECLARE C1 CURSOR WITH RETURN FOR S1;
SET v_stmt= 'select relmaint.relno,businessdate,fieldname,deptcode,use rgrp,rel.salutationcode,rel.firstname,rel.middlena me,rel.lastname,relmaint.newvalue, relmaint.oldvalue, OldCity.name as OldCityName,NewCity.name as NewCityName, OldCntry.name as OldCntryName, NewCntry.name as NewCntryName from ecapsebbs.relmaint as relmaint left outer join ecapsebbs.rel as rel on (rel.relationshipno = relmaint.relno) left outer join ecapsebbs.reladdr as reladdr on (reladdr.relationshipno = relmaint.relno and reladdr.addtypecode=''CAI'') left outer join ecapsebbs.city as OldCity on (relmaint.oldvalue = OldCity.citycode) left outer join ecapsebbs.city as NewCity on (relmaint.Newvalue = NewCity.citycode) left outer join ecapsebbs.cntry as OldCntry on (relmaint.oldvalue = OldCntry.COUNTRYCODE) left outer join ecapsebbs.cntry as NewCntry on (relmaint.Newvalue = NewCntry.COUNTRYCODE) where relmaint.businessdate > ?';
PREPARE S1 FROM v_stmt;
OPEN C1 USING DateCriteria;
END
DB20000I The SQL command completed successfully.

db2 "call ecapsebbs.relmaintsl('2003-08-11'
)"

Return Status = 0

DIRECT SELECT QUERY
--------------------------
db2 -vtf 1
select relmaint.relno,businessdate,fieldname,deptcode,use rgrp,rel.salutationcode,rel.firstname,rel.middlena me,rel.lastname,relmaint.newvalue, relmaint.oldvalue, OldCity.name as OldCityName,NewCity.name as NewCityName, OldCntry.name as OldCntryName, NewCntry.name as NewCntryName from ecapsebbs.relmaint as relmaint left outer join ecapsebbs.rel as rel on (rel.relationshipno = relmaint.relno) left outer join ecapsebbs.reladdr as reladdr on (reladdr.relationshipno = relmaint.relno and reladdr.addtypecode='CAI') left outer join ecapsebbs.city as OldCity on (relmaint.oldvalue = OldCity.citycode) left outer join ecapsebbs.city as NewCity on (relmaint.Newvalue = NewCity.citycode) left outer join ecapsebbs.cntry as OldCntry on (relmaint.oldvalue = OldCntry.COUNTRYCODE) left outer join ecapsebbs.cntry as NewCntry on (relmaint.Newvalue = NewCntry.COUNTRYCODE) where relmaint.businessdate >'2003-08-11'

output
---------

51 record(s) selected.