PRB: Can't Use More Than 2 Subqueries in SELECT-SQL Statement

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

Last Reviewed: June 27, 1995