PRB: "Error Correlating Fields" with SQL SELECT StatementID: Q109302 The information in this article applies to:
SYMPTOMSIn an SQL SELECT statement, an "Error Correlating Fields" error message occurs. CAUSEThe syntax for the outer reference is not correct. RESOLUTIONAn 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 INFORMATIONThe 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 |
