>> Hi, in the test SP below, if I use a non-grouped aggregation inside
>> nested FOR SELECT loops on the same table before updating a column
>> used in the outer loop, then the LEAVE statement exits both loops,
>> not just the inner one.
>>
>> Removing any one of
>> "and the FLT_UPR is null" from the outer loop, or
>> "select MAX(AHIGH)...", or
>> "update PRICES set FLT_UPR = :high..."
>> seems to allow LEAVE to just exit the inner loop.
>>
>> Any suggestions please? I'm using FB V1.5.6.5026
>>
>> create or alter procedure TEST_PROC(COMPANY_PK integer)
>> returns (aDate date, bDate date)
>> as
>> declare high decimal(9,3);
>> declare targetVol float;
>> declare totVol float;
>> declare vol float;
>> begin
>> for select ADATE -- outer loop using FLT_UPR
>> from PRICES
>> where FK_COMPANY = :COMPANY_PK and FLT_UPR is null
>> order by ADATE desc
>> into :aDate
>> do
>> BEGIN
>> totVol = 0;
>> targetVol = 1;
>> for select ADATE, VOL -- inner loop
>> from PRICES
>> where FK_COMPANY = :COMPANY_PK and ADATE <= :aDate
>> order by ADATE desc
>> into :bDate, :vol
>> do
>> begin
>> totVol = totVol + vol;
>> if (totVol >= targetVol) then
>> BEGIN
>> select MAX(AHIGH) -- non-grouped aggegation
>> from PRICES
>> where FK_COMPANY = :COMPANY_PK
>> into :high;
>> update PRICES
>> set FLT_UPR = :high -- FLT_UPR used in outer loop
>> where FK_COMPANY = :COMPANY_PK and ADATE = aDate;
>> leave;
>> END
>> suspend;
>> end
>> suspend;
>> END
>> end^
>>
>> Thanks,
>> Steve
>
>As usual, writing about it helps. I'm guessing that updating FLT_UPR
>is invalidating the outer For-Select loop and is probably a no-no.
>Perhaps the MAX() aggregation is an annoying red herring. Do I need
>to write my updated values to a temporary table, then transfer them
>once my loops have finished?

Hi Steve!

For some strange reason, I don't see your two messages on the yahoo groups list, even though I did receive copies of them when you sent them. So maybe some received them and some not?

To me, it looks strange to update a table in a selectable stored procedure (using SUSPEND indicates it will be called through statements like 'SELECT aDate, bDate from TEST_PROC(:Company_ID)', but I don't know much about stored procedures (only use them for very basic stuff myself), so others will have to tell you whether that is OK or not. What I can tell you, is that I would find it a little bit confusing as a programmer to discover things being updated when I do a select, and that I wonder what would happen if you selected from the procedure in a read-only transaction.

I think it might be possible to solve your actual case using a statement like:

update prices upd
set flt_upr = (select max(pmax.ahigh) from prices pmax where upd.fk_company = pmax.fk_company)
where (select sum(vol) from prices SumMe
where SumMe.fk_company = upd.fk_company
and SumMe.ADate <= upd.aDate
and not exists(select * from rdb$database
where (select sum(vol) from prices SumMeNot
where SumMeNot.fk_company = upd.fk_company
and SumMeNot.ADate between SumMe.ADate+1 and upd.aDate
and SumMeNot.ADate > SumMe.ADate) >= 1)) >= 1
and upd.fk_company = :company_pk

However, I don't know whether it works and I'm not convinced it is as readable as you should be able to get it in a stored procdure. Using CTEs, I'm pretty certain it would be possible to write a more readable query, but CTEs aren't available in Firebird 1.5.

Another option that ought to work (once you eliminate possible mistakes), is to replace your inner loop with something like

SELECT FIRST 1 ADATE, sum(VOL)
FROM PRICES
where FK_COMPANY = :COMPANY_PK and ADATE <= :aDate
GROUP BY 1
HAVING sum(VOL) >= :targetVol
order by ADATE desc
into :bDate, :vol

The idea being eliminating the need for the LEAVE statement by only selecting the one row of interest.

HTH,
Set