Hello Mitchell,

MP> Actually, there is more wrong then the for loop in the wrong place.
MP> Try this..

MP> CREATE PROCEDURE GET_missing_tickets RETURNS ( Missing_number int ) AS
MP> declare variable first_ticket integer;
MP> declare variable ticket_NMBR INTEGER;
MP> begin
MP> select min(ticket_code)
MP> from deliveries
MP> into :first_ticket ;
MP> first_ticket=first_ticket-1;
MP> For select ticket_code
MP> from deliveries
MP> into :ticket_NMBR Do
MP> begin
MP> if (ticket_NMBR<>first_ticket+1) then
MP> begin
MP> Missing_Number=first_Ticket;
MP> suspend;
MP> first_ticket=first_ticket+1;
MP> end
MP> end
MP> end
This would misbehave too, since FOR SELECT returns non-sequental
numbers (in general).
-----
CREATE PROCEDURE GET_missing_tickets
RETURNS (Missing_number integer)
AS
declare variable last_ticket integer;
declare variable ticket integer;
begin
select min(ticket_code)
from deliveries
into :ticket;
select max(ticket_code)
from deliveries
into :last_ticket;

/* omit the first and the last */
ticket = ticket + 1;
last_ticket = last_ticket - 1;

while (ticket <= last_ticket) do
begin
if (exists(select 1 from deliveries
where ticket_code = :ticket)) then
begin
Missing_number = ticket;
suspend;
end
ticket = ticket + 1;
end
end
-----
This needs DESC index on ticket_code.


--
HTH
Best regards,
Pavel Menshchikov
http://www.ls-software.com