Microsoft KB Archive/124203

{| = PRB: SQL - SELECT Error:  is not a memory variable =
 * width="100%"|

ID: Q124203

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6, 2.6a
 * Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b, 2.6, 2.6a
 * Microsoft FoxPro for Macintosh, version 2.5b, 2.5c, 2.6a

SYMPTOMS
In an SQL Select statement, if all the tables referenced in the query are not included in the FROM clause, the command fails with this error message:

 is not a memory variable.

CAUSE
FoxPro does not evaluate the table aliases referenced in a WHERE clause. This is by design and is standard SQL behavior regardless of the dialect. All SQL statements have certain required components and one such component is the list of tables that fields will be drawn from and comparisons will be made against. Without a complete list of tables, FoxPro interprets the field referenced in the right half of the WHERE clause to be a memory variable, and because there is no such variable the command fails.

RESOLUTION
To resolve this problem, the SQL statement must have all tables referenced in the SELECT statement listed in the FROM clause:

CLOSE ALL USE SYS(2004)+"\TUTORIAL\Customer" IN 1 USE SYS(2004)+"\TUTORIAL\Invoices" IN 2 SELECT company,contact ;

FROM customer,invoices ; WHERE customer.cno = invoices.cno It isn't necessary to have all the tables open. FoxPro opens the tables it needs to complete the operation. However, FoxPro can't open a table for use if it doesn't know which table it needs.

STATUS
This behavior is by design.

Example to Reproduce Behavior
In the following code example, the SQL statement is extracting two fields from the customer table, but it is doing so based on a comparison of values between the CNO fields in the Customer and Invoices tables. In the FROM clause, only one table is referenced. Consequently, the command fails.

CLOSE ALL USE SYS(2004)+"\TUTORIAL\Customer" IN 1 USE SYS(2004)+"\TUTORIAL\Invoices" IN 2 SELECT company,contact ;

FROM customer ; WHERE customer.cno = invoices.cno Additional reference words: 2.00 2.50 2.50a 2.50b 2.50c 2.60 2.60a FoxMac FoxWin FoxDos KBCategory: kbprg kbprb KBSubcategory: FxprgSql
 * }