Difference between revisions of "Microsoft KB Archive/103031"

From BetaArchive Wiki
(importing KB archive)
 
m (Text replacement - """ to """)
 
Line 12: Line 12:
 
<div id="TitleRow">
 
<div id="TitleRow">
  
= <span id="KB103031"></span>You receive the &quot;SQL expression too complex&quot; error message if the IN clause includes more than 25 values in FoxPro =
+
= <span id="KB103031"></span>You receive the "SQL expression too complex" error message if the IN clause includes more than 25 values in FoxPro =
  
  
Line 55: Line 55:
 
== SYMPTOMS ==
 
== SYMPTOMS ==
  
The &quot;SQL expression too complex&quot; error message appears if you have used 25 or more values in the IN clause of the SELECT-SQL command.
+
The "SQL expression too complex" error message appears if you have used 25 or more values in the IN clause of the SELECT-SQL command.
  
 
</div>
 
</div>
Line 64: Line 64:
 
The maximum number of values is 24 that you can include in the '''IN''' clause.<br />
 
The maximum number of values is 24 that you can include in the '''IN''' clause.<br />
 
<br />
 
<br />
'''Note''' Microsoft Visual FoxPro 9.0 removes the limit of 24 values in the '''IN (Value_Set)''' clause for the '''WHERE''' clause. However, the number of values remains subject to the setting of &quot;SYS(3055) – FOR and WHERE Clause Complexity.&quot;
+
'''Note''' Microsoft Visual FoxPro 9.0 removes the limit of 24 values in the '''IN (Value_Set)''' clause for the '''WHERE''' clause. However, the number of values remains subject to the setting of "SYS(3055) – FOR and WHERE Clause Complexity."
  
 
</div>
 
</div>
Line 71: Line 71:
 
== RESOLUTION ==
 
== RESOLUTION ==
  
See the &quot;More Information&quot; section for an example of the problem and a workaround.
+
See the "More Information" section for an example of the problem and a workaround.
  
 
</div>
 
</div>
Line 88: Line 88:
 
'16','17','18','19','20','21','22','23','24','25')
 
'16','17','18','19','20','21','22','23','24','25')
 
                     </pre>
 
                     </pre>
<p>The &quot;SQL expression too complex&quot; error message appears.</p></li></ol>
+
<p>The "SQL expression too complex" error message appears.</p></li></ol>
  
 
'''Visual FoxPro 3.0, 5.0, 6.0'''
 
'''Visual FoxPro 3.0, 5.0, 6.0'''
 
<ol>
 
<ol>
 
<li><p>Use the Customer.dbf table. In Visual FoxPro 3.0 and 5.0, Customer.dbf is located in the Samples\Data folder. In Visual FoxPro 6.0 and later, use this command:</p>
 
<li><p>Use the Customer.dbf table. In Visual FoxPro 3.0 and 5.0, Customer.dbf is located in the Samples\Data folder. In Visual FoxPro 6.0 and later, use this command:</p>
<pre class="codesample">USE HOME(2)+&quot;data\customer.dbf&quot;
+
<pre class="codesample">USE HOME(2)+"data\customer.dbf"
 
                     </pre></li>
 
                     </pre></li>
 
<li><p>Issue the following SELECT-SQL command:</p>
 
<li><p>Issue the following SELECT-SQL command:</p>
Line 100: Line 100:
 
   'BSBEV','CACTU','CENTC','CHOPS','COMMI','CONSH','DRACD','DUMON','EASTC','ERNSH',;
 
   'BSBEV','CACTU','CENTC','CHOPS','COMMI','CONSH','DRACD','DUMON','EASTC','ERNSH',;
 
   'FAMIA','FISSA','FOLIG','FOLKO','FRANK')</pre>
 
   'FAMIA','FISSA','FOLIG','FOLKO','FRANK')</pre>
<p>The &quot;SQL expression too complex&quot; error message appears.</p></li></ol>
+
<p>The "SQL expression too complex" error message appears.</p></li></ol>
  
 
=== Workaround ===
 
=== Workaround ===

Latest revision as of 08:24, 20 July 2020

Knowledge Base


Article ID: 103031

Article Last Modified on 3/10/2005



APPLIES TO

  • Microsoft FoxPro 2.5b
  • Microsoft FoxPro 2.5a
  • Microsoft FoxPro 2.0
  • Microsoft FoxPro 2.5b for MS-DOS
  • Microsoft FoxPro 2.5a
  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft Visual FoxPro 3.0b Standard Edition
  • Microsoft Visual FoxPro 5.0 Standard Edition
  • Microsoft Visual FoxPro 5.0a
  • Microsoft Visual FoxPro 6.0 Professional Edition
  • Microsoft Visual FoxPro 7.0 Professional Edition
  • Microsoft Visual FoxPro 8.0 Professional Edition



This article was previously published under Q103031

SYMPTOMS

The "SQL expression too complex" error message appears if you have used 25 or more values in the IN clause of the SELECT-SQL command.

CAUSE

The maximum number of values is 24 that you can include in the IN clause.

Note Microsoft Visual FoxPro 9.0 removes the limit of 24 values in the IN (Value_Set) clause for the WHERE clause. However, the number of values remains subject to the setting of "SYS(3055) – FOR and WHERE Clause Complexity."

RESOLUTION

See the "More Information" section for an example of the problem and a workaround.

MORE INFORMATION

Steps to Reproduce Problem

FoxPro 2.0, 2.5, or 2.6

  1. Use the Customer.dbf table in the Tutorial directory.
  2. Issue the following SELECT-SQL command:

    Select * from customer where cno in;
    ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15',;
    '16','17','18','19','20','21','22','23','24','25')
                        

    The "SQL expression too complex" error message appears.

Visual FoxPro 3.0, 5.0, 6.0

  1. Use the Customer.dbf table. In Visual FoxPro 3.0 and 5.0, Customer.dbf is located in the Samples\Data folder. In Visual FoxPro 6.0 and later, use this command:

    USE HOME(2)+"data\customer.dbf"
                        
  2. Issue the following SELECT-SQL command:

    Select * from customer where cust_id in;
    ('ALFKI','ANATR','ANTON','AROUT','BERGS','BLAUS','BLONP','BOLID','BONAP','BOTTM',;
       'BSBEV','CACTU','CENTC','CHOPS','COMMI','CONSH','DRACD','DUMON','EASTC','ERNSH',;
       'FAMIA','FISSA','FOLIG','FOLKO','FRANK')

    The "SQL expression too complex" error message appears.

Workaround

  1. In FoxPro 2.x, create a temporary table with one field in it called CNO. In Visual FoxPro, name the field Cust_id. The Temp table should contain the values in the CNO or Cust_id field that you want to find in the Customer table.
  2. Issue one of the following SELECT-SQL commands depending on your version of FoxPro:

    ** For FoxPro 2.x use this line
    Select * from customer where cno in (select cno from temp)
    
    ** For Visual FoxPro, use this line
    Select * from customer where cust_id in (select cust_id from temp)
                        



Additional query words: FoxDos FoxWin

Keywords: KB103031