Causes of common SELECT-SQL error messages (109580)


The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft Visual FoxPro for Windows 6.0
  • Microsoft Visual FoxPro for Windows 7.0
  • Microsoft Visual FoxPro 8.0
  • Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q109580

SUMMARY

The following error messages can be produced by improper usage of the SELECT-SQL statement:
SQL column <field> not found <field> is not a memory variable Alias <alias> not found
The code sample below illustrates some possible causes of these error messages and explains why they occur.

MORE INFORMATION

The following code sample executes a variety of SELECT-SQL statements. If the SELECT-SQL statement produces an error message, the reasoning behind the error message is explained. 
CREATE TABLE customer (company C(20),state C(2))
   INSERT INTO customer (company,state) VALUES ;
    ("ABC Company","WA")
   INSERT INTO customer (company,state) VALUES ;
    ("Main Street Store ","NY")

   CLOSE ALL

   USE customer ALIAS test
SET STEP ON 
   SELECT customer.company FROM test WHERE customer.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * customer.company does not exist. The table is aliased as 'test'.
    * This should be: "test.company"
    * customer.state does not exist. The table is aliased as 'test'.
    * "customer.state" should be "test.state"

   SELECT customer.company FROM test WHERE test.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * customer.company does not exist. The table is aliased as 'test'.
    * "customer.company" should be "test.company"

   SELECT test.company FROM test WHERE customer.state = "WA"

    * Produces Error: 'STATE' is not a memory variable
    * Produces Error:  SQL Column 'STATE' is not found 
    * in Visual FoxPro 6.0 and later
    * customer.state does not exist. The table is aliased as 'test'.
    * "customer.state" should be "test.state"

   SELECT test.company FROM test WHERE test.state = "WA"

    * OK.  This is a valid SELECT statement.

   SELECT customer.company FROM customer WHERE customer.state = "WA"
     * No error in VFP 6.0 and later
     
    * Produces Error: SQL column 'COMPANY' not found
    * Since the customer table is already open, FoxPro will try
    * to use it. It won't go to disk to open the table. But
    * the table is aliased as 'test' so the fields should be
    * aliased as 'test'.
    * customer.company does not exist. The table is aliased as 'test'.
    * "customer.company" should be "test.company"

   SELECT customer.company FROM customer WHERE test.state = "WA"
    * No error in VFP 6.0 and later
    
    * Produces Error: SQL column 'COMPANY' not found
    * Since the customer table is already open, Fox will try
    * to use it. It won't go to disk to open the table. But
    * the table is aliased as 'test' so the fields should be
    * aliased as 'test'.
    * customer.company does not exist. The table is aliased as 'test'.
    * "customer.company" should be "test.company"

   SELECT test.company FROM customer WHERE customer.state = "WA"
     * No error in VFP 6.0 and later
     
    * Produces Error: 'STATE' is not a memory variable
    * Since the customer table is already open, FoxPro will try
    * to use it. It won't go to disk to open the table. But
    * the table is aliased as 'test' so the fields should be
    * aliased as 'test'.
    * customer.state does not exist. The table is aliased as 'test'.
    * "customer.state" should be "test.state"

   SELECT test.company FROM customer WHERE test.state = "WA"

    * OK. This is a valid SELECT statement.

   CLOSE ALL

   USE customer ALIAS customer

   SELECT customer.company FROM customer WHERE customer.state = "WA"

    * OK. This is a valid SELECT statement.

   SELECT customer.company FROM customer WHERE test.state = "WA"

    * Produces Error: Alias 'TEST' is not found
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.state" should be "customer.state"

   SELECT test.company FROM customer WHERE customer.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * Produces Error: "Alias 'Test' is not found."
    * in Visual FoxPro 6.0 and later
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.company" should be "customer.company"

   SELECT test.company FROM customer WHERE test.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * Produces Error: "Alias 'Test' is not found."
     * in Visual FoxPro 6.0 and later
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.company" should be "customer.company"

   CLOSE ALL

   SELECT customer.company FROM customer WHERE customer.state = "WA"

    * OK.  This is a valid SELECT statement.

   SELECT customer.company FROM customer WHERE test.state = "WA"

    * Produces Error: "Alias 'TEST' not found."
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.state" should be "customer.state"

   SELECT test.company FROM customer WHERE customer.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * Produces Error: "Alias Test is not found."
    * in Visual FoxPro 6.0 and later
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.company" should be "customer.company"

   SELECT test.company FROM customer WHERE test.state = "WA"

    * Produces Error: SQL column 'COMPANY' not found
    * Produces Error: "Alias 'Test' is not found."
    * in Visual FoxPro 6.0 and later
    * There is no table named test.
    * No table is opened with a alias of test.
    * "test.company" should be "customer.company"
Modification Type:MajorLast Reviewed:3/9/2005
Keywords:kberrmsg KB109580
©2004 Microsoft Corporation. All rights reserved.