PRB: "Error Correlating Fields" with SQL SELECT Statement

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

SYMPTOMS

In an SQL SELECT statement, an "Error Correlating Fields" error message occurs.

CAUSE

The syntax for the outer reference is not correct.

RESOLUTION

An outer-reference is the part of a subquery that references a table that is not specified in the FROM clause of the subquery. An outer reference can have only the following syntax:

   table.x=table.y

MORE INFORMATION

The following code will produce an "Error Correlating Fields" error message:

   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) )

   CREATE TABLE invoices ;
      ( ino N(4), cno C(5), idate D, itotal N(8), ;
      salesman C(3) )

   INSERT INTO customer (cno, company, contact, address, ;
      city, state, zip, ;
      phone, ono, ytdpurch, lat, long) ;
      VALUES ('a123', '1st Company', 'No Name', 'Oak Tree Lane', ;
      'Any City', 'WA', '98052', '1206123456', '1', ;
      1000.99, 100.999, 100.999)

   INSERT INTO invoices (ino, cno, idate, itotal, salesman) ;
      VALUES (9999, 'A123', {09/01/93}, 1000.99, 'Bob')

   * This SELECT statement will generate the "Error Correlating Fields"
   * error message:

   SELECT * FROM customer, invoices INTO CURSOR new ;
      WHERE customer.cno=invoices.cno AND NOT EXISTS ;
      (SELECT * ;
      FROM invoices ;
      WHERE customer.cno = 'A123')

   * The following code has the correct syntax and will not cause an error:
   *
   * SELECT * FROM customer, invoices INTO CURSOR new ;
   *    WHERE customer.cno=invoices.cno AND NOT EXISTS ;
   *    (SELECT * ;
   *    FROM invoices ;
   *    WHERE customer.cno = invoices.cno)