Alan Davies wrote:

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

Your for is just in the wrong place.

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

correction...

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

> for select ticket_code
> from deliveries
> into :ticket_NMBR
>

Do Begin

> if (ticket_NMBR<>first_ticket+1) then
> begin
> suspend;
> first_ticket=first_ticket+1;
> end
> end
>end
>
>