foxpro 9.0 RECNO() RRS feed

  • Question

  • hi, anyone knows? i hd 1 table (tableA) inc only 1 attribute (Cvalue: int). how can i display every 10th value? means 10th, 20th, 30th ....

    software: foxpro 9.0

    Wednesday, August 15, 2018 2:36 AM

All replies

  • Hi cdahs,

    this depends on the content. In case there are no gaps in the value (1,2,3,4,5,6,7,8,9,0,11,12,....) this select can do this.

    SELECT * FROM tableA WHERE MOD(Cvalue,10) = 0

    However, with gaps you would have to switch to this

    SELECT * FROM tableA WHERE MOD(RECNO(),10) = 0


    Gruss / Best regards
    Debugging is twice as hard as writing the code in the first place.
    Therefore, if you write the code as cleverly as possible,
    you are, by definition, not smart enough to debug it. 010101100100011001010000011110000101001001101111011000110110101101110011

    Wednesday, August 15, 2018 6:09 AM
  • This will depend in which order, if you want every 10the value after the dat is ordered by cvalue, then you can't use MOD(RECNO(),10)=0 (or anotehr number, whereever you want to start.

    Instead you'd not do this with a query but by SKIP 10 (which means step 10 forward,not skip over 10 to the 11th, ie SKIP 1 just moves to the next row in index order):

    USE Yourtable
    INDEX ON cValue Tag xValue

    (this only once, perhaps even after creating the table empty,indexes are mainteined by inserts and updatees and deletes, you never redo them, once they exist.)

    Select YourTable
    Set Order To Tag xValue Scan ? cValue Skip 9 Endscan

    In this case the SCAN...ENDSCAN does Skip 1 itself, therefore YOU only do Skip 9, this could error, when that tries to skip past the last row, EOF.

    Select YourTable
    Set Order To Tag xValue
    Locate Do While !Eof() ? cValue Skip 10 Enddo

    More logical in this case, but you need to do the SKIP yourself, no automatic iteration of records is done.
    This ALSO will error, when you skip past EOF.

    To prevent errors, do the SKIP while a specific simple error handler is in effect, that suppresses any error:
    ON ERROR *

    That instructs VFP to handle every error (exception) by executing a comment (* is the simplest way to write a comment). So do that before the code and you get no errors.

    Warnning: You also donÄt get an other errors your code might have, so this is onl to be used cautious. You would also reset error handling back to how it was this way:

    lcErrorhandling = ON('ERROR')
    ON ERROR *
    USE dkfjhsdkfjdks && a line of code erroring will not cause an error messagebox.
    If Empty(lcErrorhandling) && this means system error handling was used, no specific error handling code
       ON ERROR
       ON ERROR &lcErrorHandling

    Bye, Olaf.

    • Edited by OlafDoschke Wednesday, August 15, 2018 6:47 PM
    Wednesday, August 15, 2018 6:00 PM
  • If this is purely for display, I'd probably use a SQL query, something like this:

    SELECT <the fields>, RECNO() AS nRec
      FROM (SELECT <the fields> ;
              FROM YourTable;
              ORDER BY <the appropriate fields>) Ordered ;
      HAVING MOD(nRec, 10) = 0 ;
      INTO CURSOR csrResult

    The inner query puts the data in the desired order. The outer query selects every 10th record.


    Wednesday, August 15, 2018 8:20 PM
  • Just some comment on Tamars solution:

    1. It's fine. It has the downside of first creating a temp result, and that it works and RECNO() actually counts up means this temp result is real and not just a virtual result not really created and optimized by rushmore.

    I'd prefer direct usage of an index and no use of RECNO, but using SKIP. You might need a query using ODBC or OLEDB Provider, but you can make use of scripts, too. especially with the OLEDB Provider and running a single EXECSCRIPT function call. The SETRESULTSET() function (used within a script you execute with EXECSCRIPT can also mark any workarea used by that script to accumulate data as the result a simple query would normally be. At least this way you can execute any VFP code (commands supported by the VFP oledb provider is restricted anyway) to create resultsets.

    2. In this case you can also do HAVING MOD(RECNO(),10), no need to create an nRec field in the result.

    3. RECNO() within VFP queries always is a bit of a concern, but doable here since it's only in in outer query on a temp result. That inner query even can get complex and have joins, since you only number the temp result as aftermath you have one concrete RECNO numbering of records.

    Bye, Olaf.

    • Edited by OlafDoschke Friday, August 17, 2018 1:07 PM
    Friday, August 17, 2018 8:05 AM