Roger Pullen schrieb:

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

Roger,

beside moving up the SUSPEND in the right block, how about using a GROUP
BY, like:

FOR SELECT
G.GRANT_FORM_NUMBER, G.REFNUM,P.MEASURE_CODE,M.MEASURE, COUNT(*)
FROM
GRANTFORMS G
LEFT JOIN PRICE_MATRIX P ON P.SCHEME_CODE = G.SCHEME_CODE
LEFT JOIN MASTER_MEASURE M ON M.MEASURE_CODE = P.MEASURE_CODE
WHERE
G.GRANT_BATCH = :gef_batch
GROUP BY
G.GRANT_FORM_NUMBER, G.REFNUM, G.GRANT_BATCH,
P.MEASURE_CODE
ORDER BY
G.GRANT_FORM_NUMBER, G.REFNUM, G.GRANT_BATCH,
P.MEASURE_CODE
into
:gef,:refnum,:measure_code,:measure, :MEASURECOUNT
do begin
IF ( MEASURECOUNT > 0 ) THEN REQUESTED = 'Y';
ELSE REQUESTED = '';
SUSPEND;
end


Luc.