use the reccount

use the reccount

• bat313

  Thanks, Alex. It is very helpful.
  
  

• Shivakumara V

  Interesting!

  Additional questions: You mean we should use SELECT ALL .. FROM.. WHERE <filter condision> .

  How do I address the resulting set Does it have the same alias name as the original table or different What does "local_alias" mean in the command description Is it an alias you give to this subset of the original table alias

  Will the RECCOUNT() command reflect the resulting subset of the SELECT SQL command, that is the true number of records in it

  Thanks.

  
  
  

• John Askew

  Hi,

  i think you will need to use COUNT command instead. RECCOUNT() is not affected by SET FILTER.

  
  
  

• RGIMatt

  Alex I have no doubt that the syntax you've shown me is correct ut I am getting an error message in a similar situation. Obviously I am doing something weird:

  SELECT * FROM cTableAlias ;

  WHERE (cTableAlias.parentDir = Dir0 AND cTableAlias.marks = .F.) ;

  INTO CURSOR cLocAlias

  cLocAlias

  The error message is that the "variable cLocAlias is not found."

  I thought that the cursor was supposed to be created. Shall I initialize cLocAlias somehow Perhaps I shall say: cLocAlias = "cLocAlias" or something When I did this I got an error message at a different point (down the execution chain) that "'cLocAlias' must be created with  with SELECT...INTO TABLE command." I created it with SELECT ... INTO CURSOR command.

  Thanks

  
  
  

• dfad44Kyle

  Well, you may say I am crazy but for me, with my compiler, at least, the statements you quoted do not work. The second statement breaks down, it gives an error.

  Also I've discovered a nasty surprise that has been a sourse of some of my debugging trouble. The SELECT statement creates a cursor (or a TABLE as they say) with field names truncated down to 10 chars.

  This variant works for me:

  Select * From Customers Where State = "NJ" Into TABLE NJ_Customers

  Select * From NJ_Customers Where City = "Trenton" Into TABLE CityCustomers

  It appears that in fact a temporary table, that is a cursor is created.

  It is unfortunate that creation of a cursor cannot be easily verified as I see at this point at least.

  Thanks.

   

  
  
  

• Whatonly

  SET FILTER is a very old XBase command that should be avoided.

  Unless you have a smallish set and full Rushmore optimization, SET FILTER can become very slow.

  A better alternative is to do a SQL query to obtain a result set. As always Rushmore optimization is important (read all about it in the help files).

  That said, Reccount() is not affected by SET FILTER. You would have to use COUNT like this:

  COUNT to nVariable

  or

  COUNT FOR {condition} to nVariable

   

  
  

• drourke

  Well, I expect it to display some information, for instance that it is in object or a cursor or whatever. This is what you get when in VdBase you say object.
  
  

• Rashid Farooq

  This is what I get in the next statement:

  'cLocAlias' must be created with SELECT ... INTO TABLE. This statement is:

  SELECT * FROM cLocAlias ;

  WHERE cLocAlias.marks = .F. ;

  INTO CURSOR cLocAlias2

  
  
  

• Ryan98034

  Alex hi,

  It seems to me I understand this. Your explanation just reinforced it. I understand that the cursor is a temporary table.

  I want to have a convenience, however, of checking the fact that the cursor has been created. My program is very loopy and it skips blocks routinely based on some conditions. That's why I tried that statement cLocAlias. BTW it worked for another alias (a table alias) for me. I do not understand why it did not work here. I tried BROWSE as Tamar saggested and it worked, it showed that the cursor was created.

  I do not understand why I get an error message when I try to SELECT from cLocLaias <cursor>. You've showed to me that it must work. I will keep trying.

  Thanks.

  
  
  

• bob laros

  This is how it works for me. Finally.

  Select * From Customers Where State = "NJ" Into Cursor NJ_Customers NOFILTER

  Then a second query can be taken, otherwise the runtime does not recognize NJ_Customers for anything but RECCOUNT() on NJ_Customers right after the Select statement.

  Select * From NJ_Customers Where City = "Trenton" Into Cursor CityCustomers

  Without the clause NOFILTER the second Select cannot work.

  There is one more "trouble." I have discovered that SELECT statement does not carry index tags with it over to the cursor. Am I right Thus if I need an index subsequently I have to use INDEX on the cursor

  There is one more thing that stands on my way now although it seems I am beginning to get used to VFP idiosyncrasies. When the first cursor is created, the original table disappears from the work are. Is it normal or my imagination only I do not seem to be able to find it.

  Another surprise. I mentioned in my previous post that SELECT ... INTO TABLE truncates the field names. It is true. But SELECT INTO CURSOR <...> NOFILTER READWRITE does not!

  I will appreciate any help, especially as to how  preserve the original table in WA.

  Thanks.

  
  
  

• objectref

  Alex:

  You seem to be confusing VFP tables withCursors with memory variables.

  Is 'cLocAlias' the name of your cursor, or a memory variable that contains the name of your cursor (probably not needed this way)

  Basic syntax:

  Select * from MyTable Where MyCondition Into Cursor MyCursor

  MyCursor is now a VFP cursor (table in memory that works just like a VFP Table). You can Browse or do almost anything like on a table. But in most cases it is only kept as a table representation in memoty (actually on a temporary file with a random name and TMP extension). Therefore if you want to further process this table and do no use the alias and want to know the actual name of the underlying table for the cursor, you canuse the DBF() function. Noite that when a cursor is closed it disappears  (is erased).

  e.g.:

  Select * From Customers Where State = "NJ" Into Cursor NJ_Customers

  BROWSE

  Select * From NJ_Customers Where City = "Trenton" Into Cursor CityCustomers

  BROWSE

  
  

• M HOWELL

  Sure it can. As I said before, you can use the DBF() function to return the actual full name. Open a table and see what DBF() returns. Then do the same with a cursor.

   

  If you want to refer to a cursor by full name, as you did with a table in your example, use DBF().

  Select * from DBF() ...

  or

  cLocalCursor = DBF()

  Select * from (cLocalCursor) ....

   

  In the last example we are using a named expression (similar to macro substitution) to read the contents of the variable. Seemore details on named expressions and macro substitution in Help. Search for "&".

   

  
  

• Carlobas

  What do you expect cLocAlias to display cLocAlias is an alias for a table, not a variable that contains something you can print.
  
  Try BROWSE after the query to see your results.
  
  Tamar
  
  

• cb1024

  SELECT SQL in VFP uses a very good subset of SQL ANSI 92 standards. Even better in VFP 9.0

  SELECT field1, field2 ;

  FROM MyTable ;

  WHERE field3 = {some condition} ;

  INTO CURSOR MyResultSet

  Will give you a full cursor (or a table if you so choose) with the returned records. This will be opened inthe next available area. Reccount() and all other VFP and XBASE commands are available on this set.

  
  
• use the reccount