PRB: "Error Correlating Fields" with SQL SELECT Statement

ID: Q109302

The information in this article applies to:

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

Syntax containing literal values, such as table.x=table.y+1 or table.x=5, will produce an error. See below for a code example that demonstrates the problem and the solution.

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)

Additional reference words: FoxMac FoxDos FoxWin 2.50 2.50a 2.50b correlate errmsg err msg KBCategory: kbprg kberrmsg kbprb KBSubcategory: FxprgSql

Last Reviewed: June 27, 1995