PRB: SQL - SELECT Error: <Field_name> is not a memory variable

• 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:

   <Field name> 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

STATUS

This behavior is by design.

MORE INFORMATION

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