I am dealing with two tables here. I am performing a scan over table1, and then inside that I am performing a do while loop over table2. So for each row in table1, there is a do while loop being performed on only some of the rows in table2. Every time the do while loop starts again, it seems to start iterating from the top of table2. How do I make it so that it iterates at a specific new location in table2 each time, which happens to be the row where the previous do while loop stopped. So essentially, every time the do while loop starts up I need it to start at the row in which it previously left off in table2.
Here is a pseudo code that seems to work now (not sure how efficient it is):
CLEAR
SELECT table1
SCAN
SELECT table2
SCAN FOR table2.id = table1.id
IF table2.type = 1 THEN
?'type 1'
ELSE
?'type 0'
ENDIF
ENDSCAN
ENDSCAN
I also thought it should be possible to use scan while as the inner loop, but I have not had success with that yet. The only difference I had made was replace the scan for with scan while. From what I have read, scan for will loop through the entire table2 every time, while scan while will start the next loop at the point where the previous one left of, which I assume would be much more efficient?
For context, table1.id column looks something like: [1,2,3,4,5,...] where table2.id column looks something like: [1,1,2,2,2,3,4,4,4,4,5,5,...]. Essentially I hope to do something like this: wherever the id number matches up, I need to perform an operation on a column in table2. once I have performed it on all rows which the id number matches table1, I will have a specific value, and then I will insert that value into table1, and then continue with the same process until every row in table1 has an inputted value in the column next to the id. Hopefully this makes some sense, and sorry for all the writing.
The following test code indicates a combination of Seek command or Seek() function to find the start row if local alias' record pointer is not already there, followed by a Scan While loop might normally beat the other possibilities
Quoted from Tamar Granor's posting in the Foxite.com forum http://www.foxite.com/archives/0000386598.htm
* Compare loop speeds for tables
#DEFINE PASSES 1000
LOCAL nStart, nEnd, nPass
* Open the table
OPEN DATABASE HOME(2) + "Northwind\Northwind"
USE OrderDetails
SET TALK OFF
* First, DO WHILE
nStart = SECONDS()
FOR nPass = 1 TO PASSES
GO TOP
DO WHILE NOT EOF()
SKIP
ENDDO
ENDFOR
nEnd = SECONDS()
?"DO WHILE--", PASSES, " passes:", nEnd-nStart
* Now, SCAN
nStart = SECONDS()
FOR nPass = 1 TO PASSES
SCAN
ENDSCAN
ENDFOR
nEnd = SECONDS()
?"SCAN--", PASSES, " passes:", nEnd-nStart
* Now test with order set
SET ORDER TO PRODUCTID && PRODUCTID
* First, DO WHILE
nStart = SECONDS()
FOR nPass = 1 TO PASSES
GO TOP
DO WHILE NOT EOF()
SKIP
ENDDO
ENDFOR
nEnd = SECONDS()
?"DO WHILE with order set--", PASSES, " passes:", nEnd-nStart
* Now, SCAN
nStart = SECONDS()
FOR nPass = 1 TO PASSES
SCAN
ENDSCAN
ENDFOR
nEnd = SECONDS()
?"SCAN with order set--", PASSES, " passes:", nEnd-nStart
* Now test processing a subset of the list
nProductID = 58
* First, DO WHILE
nStart = SECONDS()
FOR nPass = 1 TO PASSES
SEEK m.nProductID
DO WHILE NOT EOF() AND ProductID = m.nProductID
SKIP
ENDDO
ENDFOR
nEnd = SECONDS()
?"DO WHILE for subset--", PASSES, " passes:", nEnd-nStart
* Now, SCAN FOR
nStart = SECONDS()
FOR nPass = 1 TO PASSES
SCAN FOR ProductID = m.nProductID
ENDSCAN
ENDFOR
nEnd = SECONDS()
?"SCAN FOR for subset--", PASSES, " passes:", nEnd-nStart
* Now, SCAN WHILE
nStart = SECONDS()
FOR nPass = 1 TO PASSES
SEEK m.nProductID
SCAN WHILE ProductID = m.nProductID
ENDSCAN
ENDFOR
nEnd = SECONDS()
?"SCAN WHILE for subset--", PASSES, " passes:", nEnd-nStart
RETURN