Microsoft KB Archive/120049

{| = PRB: Can't Use More Than 2 Subqueries in SELECT-SQL Statement =
 * width="100%"|

ID: Q120049

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5x, 2.6
 * Microsoft FoxPro for MS-DOS, versions 2.5x, 2.6
 * Microsoft FoxPro for Macintosh, versions 2.5x

SYMPTOMS
When more than two subqueries at the same level and not nested are used in the WHERE clause of a SELECT-SQL statement, the error message "SQL Too many subqueries" is displayed when the SQL statement is executed.

STATUS
Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION
The last paragraph of the WHERE clause section in the "SELECT-SQL" topic of the "Language Reference" manual and the online Help system states the following:

You can have multiple subqueries at the same level (not nested) in the WHERE clause. This information is true, but you cannot have more than two subqueries at the same level in a SELECT-SQL command, as demonstrated by the following example: &&Create Tables CREATE TABLE TEST1 (F1 C(3),F2 n(3),F3 n(3)) CREATE TABLE TEST2 (F1 C(3),F2 N(3))

&&Insert Data into TEST1.DBF INSERT INTO TEST1 (F1,F2,F3) VALUES('AAA',10,1) INSERT INTO TEST1 (F1,F2,F3) VALUES('AAA',5,10) INSERT INTO TEST1 (F1,F2,F3) VALUES('BBB',10,2) INSERT INTO TEST1 (F1,F2,F3) VALUES('BBB',5,10) INSERT INTO TEST1 (F1,F2,F3) VALUES('CCC',10,1) INSERT INTO TEST1 (F1,F2,F3) VALUES('CCC',5,10)

&&Insert Data into TEST2.DBF INSERT INTO TEST2 (F1,F2) VALUES('AAA',1) INSERT INTO TEST2 (F1,F2) VALUES('BBB',2) INSERT INTO TEST2 (F1,F2) VALUES('AAA',2) INSERT INTO TEST2 (F1,F2) VALUES('BBB',5)

&&SQL command to demonstrate the situation SELECT F1,F2,F3 FROM TEST1 WHERE F1 IN(SELECT F1 FROM TEST2) AND; F2 >(SELECT MIN(F2) FROM TEST2) AND; F3 <(SELECT AVG(F2) FROM TEST2) The above SQL statement will result in the "SQL Too many subqueries" error message when it is executed, whereas the following statement will not: SELECT F1,F2,F3 FROM TEST1 WHERE F1 IN(SELECT F1 FROM TEST2) AND; F2 >(SELECT MIN(F2) FROM TEST2) Additional reference words: FoxDos FoxWin 2.00 2.50 2.50a 2.50b 2.60 err errmsg msg KBCategory: kbprg kberrmsg kbprb KBSubcategory: FxprgSql
 * }