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
--
Alan J Davies
Aldis