Length of SQL statement

I have a user trying to submit a query on a table with a column qualification containing 5450 possible values. The user gets the following error: -460 Statement length exceeds maximum. Is there an Informix variable that can be modified to increase the length of a SQL statement? If not, is there a work around? Thank you, Tony

Demeis, Tony wrote: > I have a user trying to submit a query on a table with a column > qualification containing 5450 possible values. The user gets the following > error: > > -460 Statement length exceeds maximum. > > Is there an Informix variable that can be modified to increase the length of > a SQL statement? If not, is there a work around? Do I understand this correctly - a user is including 5,450 possible values in the WHERE clause of a SELECT? I must be wrong, right? If not, the full text of the -460 error says: The statement text in this PREPARE, DECLARE, or EXECUTE IMMEDIATE statement is longer than the database server can handle. The actual limit differs with different implementations, but it is always generous, in most cases up to 32,000 characters. Review the program logic to ensure that an error has not caused it to present a string that is longer than intended (for example, by overlaying the null string terminator byte in memory). If the text has the intended length, revise the program to present fewer statements at a time. With that in mind, I've got two suggestions. The first would be to put those 5,450 values into a table and use the IN or EXISTS subquery operators with the SELECT. The second would be to break all those values into smaller parts making several SELECT statements and try grouping them with UNION. That last one is just a wild guess; the combination of all queries with the UNION may still exceed the limit. -- June Hunt

why not put the 5450 possible values in a temporary (or permanent, depending on the nature of your app) table and do select ... from ... where column in (select x from tab_of_5450_values); Mike At 11:36 AM 9/2/04, Demeis, Tony wrote: >I have a user trying to submit a query on a table with a column >qualification containing 5450 possible values. The user gets the following >error: > >-460 Statement length exceeds maximum. > >Is there an Informix variable that can be modified to increase the length of >a SQL statement? If not, is there a work around? > >Thank you, >Tony ---------------------------------------------- Michael Dunham-Wilkie, M.Sc., M.P.A. Senior Database Analyst Barrodale Computing Services Ltd. Tel: (250) 472-4372 Fax: (250) 472-4373 Web: <http://www.barrodale.com>http://www.barrodale.com Email: mike@barrodale.com ---------------------------------------------- Mailing Address: P.O. Box 3075 STN CSC Victoria BC Canada V8W 3W2 Shipping Address: Hut R, McKenzie Avenue University of Victoria Victoria BC Canada V8W 3W2 ----------------------------------------------

The limit on SQL statements is usually 65535, the maximum number that can be represented in a 16-bit unsigned quantity. As already suggested, 5000+ values (actually, from about 10+ values) should be put into a table, possibly a temp table, and that should be used--possibly after creating an appropriate index on it and after updating statistics on it. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 09/02/2004 01:24:09 PM: > > why not put the 5450 possible values in a temporary (or permanent, > depending on the nature of your app) table and do > > select ... from ... where column in (select x from tab_of_5450_values);> > Mike > > At 11:36 AM 9/2/04, Demeis, Tony wrote: > > >I have a user trying to submit a query on a table with a column > >qualification containing 5450 possible values. The user gets the following > >error: > > > >-460 Statement length exceeds maximum. > > > >Is there an Informix variable that can be modified to increase the length of > >a SQL statement? If not, is there a work around? > > > >Thank you, > >Tony > > ---------------------------------------------- > Michael Dunham-Wilkie, M.Sc., M.P.A. > Senior Database Analyst > Barrodale Computing Services Ltd. > Tel: (250) 472-4372 Fax: (250) 472-4373 > Web: <http://www.barrodale.com>http://www.barrodale.com > Email: mike@barrodale.com > ---------------------------------------------- > Mailing Address: > P.O. Box 3075 STN CSC > Victoria BC Canada V8W 3W2 > > Shipping Address: > Hut R, McKenzie Avenue > University of Victoria > Victoria BC Canada V8W 3W2 > ---------------------------------------------- >