Testing for sequential numbers

[bobshipley@rmrelevator]

Is there a simple way to test for sequential numbers in a field?

I have table that has about 250k records and I want a list of numbers that do not exist in the table.

[Stan Mathews]

Re: Testing for sequential numbers

Does the field hold numbers (numeric) or digits (character)?

[bobshipley@rmrelevator]

Re: Testing for sequential numbers

Numbers.

[Stan Mathews]

Re: Testing for sequential numbers

I have no idea how the performance will be on that large a table but this should report the missing numbers.

tbl = table.open("atest")[COLOR="#FF0000"] 'adjust to your table name[/COLOR]
tbl.fetch_first()
missing = ""
expected = 1
tbl.batch_begin()
while .not. tbl.fetch_eof()
	if tbl.ANUM = expected [COLOR="#FF0000"]'change anum to your field holding the sequence number[/COLOR]		
          'ok
		tbl.fetch_next()
		expected = expected + 1
	else
		while tbl.anum < expected .and. (.not. tbl.fetch_eof()) [COLOR="#FF0000"]'change anum to your field holding the sequence number[/COLOR]
		missing = missing +alltrim(str(expected))+","
		tbl.fetch_next()
		expected = expected + 1
		end while
	end if
end while
tbl.batch_end()
tbl.close()
save_to_file(missing,"c:\temp\missing in sequence.txt",.F.)

This assumes the table is in order by the sequence field value. If not you can insert

tbl.order("sequence_field_name")

immediately after the table.open().

[MoGrace]

Re: Testing for sequential numbers

I couldn't get Stan's table methods to work for my function so here is what I came up with for a numeric series.

I'd be interested to know how it performs on a large table.

FUNCTION No_Series AS C (tblname AS C, fldname AS C,vStart as N )
'DESCRIPTION: Check a table for missing numbers in a series and write the result to a text file
No_Series = ""
dim tbl as p
dim vfilename as c
dim vList as c
dim vStr as c = ""
dim vCnt as n
dim vNum as N 

tbl = table.open(tblname)
dim fltr as c
fltr = convert_expression(fldname+" >= "+s_quote(vStart),"V")
vList = tbl.record_content_get(fldname,fldname,fltr)
tbl.close()

Tln(fldname+"- Starting number",vStart)
vNum = vStart-1
vfilename = a5.Get_Path()+chr(92)+"missing_"+fldname+"_"+cdate(date())+".txt"

for each x in vList
	vnum = vnum+1
	TESTIT:
	if vnum < val(x.value) then
		while vnum < val(x.value)
			vStr = vStr+ltrim(str(vnum))+crlf()
			'Tln("Missing",vStr)
			vnum = vnum+1
			goto TESTIT
		end while
	end if
next
	
save_to_file(vStr,vfilename,.f.)
sys_open(vfilename)
No_Series = vfilename
END FUNCTION

No_Series("TEST_NUM","CHECKNO",10001)

No_Series_ 071418.txt

If you import it - you will have to comment out the TLN() function or replace with a trace method.