Microsoft KB Archive/109584

{| = How to Force SELECT-SQL to Create Temp File for Cursor =
 * width="100%"|

ID: Q109584

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

WINDOWS             | MS-DOS The information in this article applies to:
 * 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.

You can force FoxPro to create the cursor with a temporary file, instead of the filtered one. 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 To verify the existence of a temporary file for a cursor, issue the following command: ".TMP"$DBF(' ') Additional reference words: VFoxWin 3.00 FoxDos FoxWin 2.50 2.50a 2.50b index on cursor KBCategory: KBSubcategory: FxprgSql Keywords         : kbcode FxprgSql Version          : 2.50 2.50a 2.50b 3.00| 2.50 2.50 Platform         : MS-DOS WINDOWS
 * }