sqlwhile-loopfoxpro

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


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.


Solution

  • 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