Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Getting Missing Numbers Between Two Values in a Table
Message
From
11/11/2017 17:42:25
Al Doman (Online)
M3 Enterprises Inc.
North Vancouver, British Columbia, Canada
 
 
To
11/11/2017 11:24:31
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Environment versions
Visual FoxPro:
VFP 9 SP2
OS:
Windows 7
Database:
Visual FoxPro
Application:
Desktop
Miscellaneous
Thread ID:
01655491
Message ID:
01655506
Views:
49
Definitely. A DBF + CDX of 10,000 integers totals about 95KB, which will fit in the cache of many modern CPUs (L2 or L3, if not L1) so it could be a big performance win in a recurring-calls scenario.

>The OP reads to me that this is a recurring requirement, so I'd exchange space for time and create AllLarge as pre-indexed table and query the range established by min/max via where ;-))
>
>>>The Range may be Between 1 to 10000 and it is not necessary that 1 will in the top or 10000 will be at the bottom of the cursor.
>>>I need Missing Figures in a Cursor (Difference in Numbers=1)
>>
>>There is a way to do it via SQL - maybe not the fastest, but relatively simple and maintainable:
>>
>>* Assume your cursor with random values has an alias RandomVals
>>LOCAL ;
>>	lnMinVal ;
>>	, lnMaxVal ;
>>	, lnIx
>>
>>* Get lowest and highest values:
>>* Option 1: index RandomVals:
>>SELECT RandomVals
>>INDEX ON fObjectNo TAG fObjectNo
>>
>>GO TOP
>>m.lnMinVal = RandomVals.fObjectNo
>
>
>>GO BOTTOM
>>m.lnMaxVal = RandomVals.fObjectNo
>>
>>* Option 2: don't want to index RandomVals:
>>*!*	CALCULATE ;
>>*!*		MIN( fObjectNo ) ;
>>*!*		, MAX( fObjectNo ) ;
>>*!*		TO ;
>>*!*			m.lnMinVal ;
>>*!*			, m.lnMaxVal ;
>>*!*		IN RandomVals
>>
>>* Create another cursor with all possible values, both present and missing:
>>CREATE CURSOR AllVals ( iVal I )
>>
>>* Populate the cursor with all possible values between m.lnMinVal and m.lnMaxVal
>>FOR m.lnIx = m.lnMinVal TO m.lnMaxVal STEP 1
>>	INSERT INTO AllVals ( iVal ) VALUES ( m.lnIx )
>>
>>ENDFOR
>>
>>* Optional - index AllVals to help speed up the final SELECT - SQL:
>>SELECT AllVals
>>INDEX ON iVal TAG iVal
>>
>>* Get missing values:
>>SELECT ;
>>	iVal ;
>>	FROM AllVals ;
>>	WHERE iVal NOT IN ;
>>		( SELECT fObjectNo FROM RandomVals ) ;
>>	INTO CURSOR MissingVals ;
>>	ORDER BY iVal
>>
>>* Cleanup:
>>USE IN SELECT( "AllVals" )
>>
Regards. Al

"Violence is the last refuge of the incompetent." -- Isaac Asimov
"Never let your sense of morals prevent you from doing what is right." -- Isaac Asimov

Neither a despot, nor a doormat, be

Every app wants to be a database app when it grows up
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform