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

>>CREATE CURSOR NUMBERS (FN I)
>>FOR K=1 TO 100
>>	INSERT INTO NUMBERS VALUES (RAND()*97+3)
>>NEXT
>>SELECT DISTINCT FN FROM NUMBERS INTO CURSOR NUMBERS
>>
>>* RETURN GAPS
>>lowerBound = 1
>>UpperBound = 100
>>SELECT m.lowerBound	GapBegin;
>>,		MIN(FN)-1	GapEnd;
>>	FROM NUMBERS N;
>>	HAVING MIN(FN)>m.lowerBound;
>>UNION ALL;
>>SELECT	N.FN+1	;
>>	,	NVL((SELECT MIN(FN) FROM NUMBERS WHERE FN > N.FN ) - 1,m.UpperBound ) ;
>>	FROM NUMBERS N;
>>	WHERE NOT EXISTS(SELECT * FROM NUMBERS WHERE FN-1 = N.FN)
>>

>
>Fabio,
>
>I just tried your code against a real world table with ~135000 records, 18MB on disk. It failed after 15 minutes with:
>
>File c:\temp\00000o3t0017.tmp is too large.
>
>?

Does this work any better?

create CURSOR sequence (nval n(3,0))
INSERT INTO sequence VALUES (1)
INSERT INTO sequence VALUES (2)
INSERT INTO sequence VALUES (4)
INSERT INTO sequence VALUES (5)
INSERT INTO sequence VALUES (6)
INSERT INTO sequence VALUES (9)
INSERT INTO sequence VALUES (11)
INSERT INTO sequence VALUES (12)
INSERT INTO sequence VALUES (15)

SELECT x.nval+1 gapstart, MIN(y.nval)-1 AS gapend ;
       FROM sequence x, sequence y ;
       WHERE x.nval < y.nval ;
       GROUP BY x.nval ;
       HAVING gapend - x.nval >= 1

It gives you a gapstart and a gapend when there are multiple missing sequential numbers. (Thanks to Walter Meester on this several years ago)