FoxPro 9.0, starting a loop at a specific row in a table

Question

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.

Stefan Wuebbe · Answer

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

FoxPro 9.0, starting a loop at a specific row in a table

1 Answers