Could someone cast an eye over this SP below
- I cannot get it to work properly

The general idea is that the first SQL (the for loop)
returns x rows (which in isolation it does) but when I add
the second SQL, which determines how many of the measure I have
it only returns the last row - or maybe I have it completely wrong

Thanks


Roger P

----------------------------------------------------------------------------
------------
create procedure gef_measures (GEF_BATCH integer)
returns (GEF integer, REFNUM integer,
MEASURE varchar(60), MEASURE_CODE varchar(10), REQUESTED varchar(1))
as
declare variable measurecount integer;
declare variable measurecode varchar(10);
begin

for SELECT DISTINCT G.GRANT_FORM_NUMBER, G.REFNUM,P.MEASURE_CODE,M.MEASURE
FROM GRANTFORMS G, PRICE_MATRIX P,MASTER_MEASURE M
WHERE
(G.GRANT_BATCH = :gef_batch)
AND (P.SCHEME_CODE = G.SCHEME_CODE)
AND (P.MEASURE_CODE = M.MEASURE_CODE)
ORDER BY G.GRANT_FORM_NUMBER, G.REFNUM, G.GRANT_BATCH,
P.MEASURE_CODE
into :gef,:refnum,:measure_code,:measure

do
begin
requested = '';
SELECT COUNT(DISTINCT MEASURE_CODE)
FROM MEASURE_INITIAL
WHERE (REF_NUM = :refnum)
AND (MEASURE_CODE = :measure_code)
into :measurecount;

if (measurecount > 0) then
requested = 'Y';
end
suspend;
end