Alan Davies wrote:

Actually, there is more wrong then the for loop in the wrong place.

Try this..

CREATE PROCEDURE GET_missing_tickets RETURNS ( Missing_number int ) AS
declare variable first_ticket integer;
declare variable ticket_NMBR INTEGER;
begin
select min(ticket_code)
from deliveries
into :first_ticket ;

first_ticket=first_ticket-1;

For select ticket_code
from deliveries
into :ticket_NMBR Do
begin
if (ticket_NMBR<>first_ticket+1) then
begin
Missing_Number=first_Ticket;
suspend;
first_ticket=first_ticket+1;
end
end
end

>Hi all
>Could someone please help with the following.
>I have a table - table1 - with e.g. ticket_NMBR integer, item_name char(10).
>And, no I cannot enforce sequential numbers because the real data comes
>from an
>external source.
>Data is
>ticket_NMBR item_name
>1 aa
>2 bb
>4 dd
>6 ff
>
>How can I get a report of the missing ticket_NMBRs?
>i.e.
>Missing Number Report
>3
>5
>
>I guess I could create a table - table_temp - on the fly with numbers
>1-6 in it
>and select ticket_NMBR from table_temp where ticket_NMBR not in table1, but
>this is not good for real life. I have tried this procedure but get a
>"multiple
>rows in singleton select" error; I think I'm on the right lines, however.
>
>CREATE PROCEDURE GET_missing_tickets
>RETURNS (
> ticket_NMBR INTEGER)
>AS
> declare variable first_ticket integer;
>begin
> for select min(ticket_code)
> from deliveries
> into :first_ticket
> do
> begin
> select ticket_code
> from deliveries
> into :ticket_NMBR;
> if (ticket_NMBR<>first_ticket+1) then
> begin
> suspend;
> first_ticket=first_ticket+1;
> end
> end
>end
>
>