Problem: When using the keyword "suspend" in an executable procedure, the result set will return just one row after the first fetch. Following is an example code to indicate the important items to check when using a "for select -- do" form of fetching in order to manipulate returning rows based on some criteria...amongst two tables.... Solution: connect employee.gdb; set term !!; create procedure count_sel (mydt date) returns (jcode char(5), jgrade integer, fcnt integer) as begin for select distinct job_code from job into :jcode do begin select count(job_grade) from employee where job_code = :jcode and (hire_date <:mydt) into :jgrade; select count(first_name) from employee where job_code=:jcode and (hire_date > :mydt) into :fcnt; suspend; end end!! set term ;!! =================================================== execute procedure count_sel("10-MAR-1990") JCODE JGRADE FCNT ====== =========== ==== Accnt 0 0 =================================================== select * from count_sel("2-MAR-1988"); JCODE JGRADE FCNT ====== =========== =========== Accnt 0 0 Admin 0 4 CEO 0 1 CFO 0 1 Dir 0 1 =================================================
Last Modified: 26-OCT-00