How to Force SELECT-SQL to Create Temp File for Cursor

ID: Q109584

2.50 2.50a 2.50b 3.00| 2.50 2.50a 2.50b

WINDOWS              | MS-DOS

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b
• Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b

SUMMARY

When the INTO CURSOR clause of the SELECT-SQL command is used to direct output to a cursor, this cursor can take one of two forms. It can be a cursor that has its own temporary file on the disk, or it can be a filtered subset of the table that the SELECT-SQL statement was based on.

A cursor can be created as a filtered view of the original table if:

• There was only one table involved in the SELECT statement.

  -and-

• There were no calculated fields in the field list.

The existence of a cursor on the hard disk provides the following benefits:

• You can create a single index.

  NOTE: You cannot create multiple indexes on the same temp file.

  - You can use the SELECT statement to select from the cursor.

MORE INFORMATION

You can force the creation of a cursor within an SELECT-SQL statement in FoxPro. To do this, use the SELECT statement to select column expressions in the field list that do not exist in the database.

In a multiple-table SELECT statement, this is accomplished when fields from both tables are selected. Since the field list does not match a table directly, FoxPro will not create a mask of the database but instead will create a temporary file on disk. If only one table exists, or if you do not want to select fields from more than one .DBF file, use the following code.

   * The following lines create tables for use in the SELECT-SQL below

   CREATE TABLE customer ;
      ( cno C(5), company C(35), contact C(20), ;
      address C(30), city C(15), state C(2), zip C(5), ;
      phone C(12), ono C(1), ytdpurch N(8,2), lat N(7,4), ;
      long N(8,4) )

   INSERT INTO customer (cno, company, contact, address, ;
      city, state, zip, ;
      phone, ono, ytdpurch, lat, long) ;
      VALUES ('a123', '1st Company', 'No Name', 'One Microsoft Way', ;
      'Redmond', 'WA', '98052', '1206123456', '1', ;
      1000.99, 100.999, 100.999)

   * The .T. in the field list causes FoxPro to create a cursor.

   SELECT Customer.cno, Customer.ytdpurch, .T. ;
    FROM Customer INTO CURSOR Query

   ".TMP"$DBF('<alias>')

Keywords          : kbcode FxprgSql 
Version           : 2.50 2.50a 2.50b 3.00| 2.50 2.50
Platform          : MS-DOS WINDOWS