Hi Hilmar..thanks for your reply.
I need Every missing number so I guess I'll do a prg for it.... just thought I'd check in case there was a nifty Sql select to do it !!
Regards,
Gerard


>>Hi.
>>Is there any syntax of a Select command that will higghlight mssing numbers.
>>I have a table which has a field which should be sequential and I want to highlight if there are any msiing numbers.
>>I can do this by writing a program but wondered if there was a way to do this using select synatax ?
>>Regards
>>Gerard
>
>I think writing a program is the best approach. The problem with SQL is that for a NOT IN, for example, you would need to construct a larger set, which again requires coding.
>
>One thing you can do with SQL is the following: for each sequence number, get the next sequence number, and compare. This would only give you the first missing number if there are several consecutive numbers, but it may be a starting point for the user to check. Something like this:
>
>

>select Sequence;
>  from MyTable;
>  into Tmp1;
>  nofilter
>select Sequence + 1 as Sequence;
>  from MyTable;
>  into Tmp2;
>  nofilter
>select Sequence from Tmp2;
>  where Sequence not in Tmp1
>

>
>And then alert the user that if there are several missing numbers, you are only showing the first in the sequence. Or use some additional (non-SQL?) coding from there, to get additional sequence numbers. You might even use SQL to get, for each missing number, the next higher number that does exist - and show the range of missing numbers. After all, you may not need to actually generate 10 consecutive missing numbers; instead, you might tell the users to check the situation.