Re: informix ms-access sort problem

This is a document you can find on Intersolv public knowledge base. "INTERSOLV KnowledgeBase Document Number: 1595DD4 Error: -309 "[INTERSOLV][ODBC Informix driver][Informix]ORDER BY column (index) must be in SELECT list." in Access Created on 01/12/96 Topic When I try to execute a query in Access or Visual Basic, I get the error message "[INTERSOLV][ODBC Informix driver][Informix]ORDER BY column (index) must be in SELECT list.", but I know I'm including the ORDER BY column in the SELECT list. What's wrong? Resolution The Microsoft Jet Engine, which both Access and Visual Basic use, always tries to optimize queries by trying to build its own internal keyset. In building this keyset, the Jet Engine tries not to retrieve data that would have to be discarded when building this keyset. As a result, even if you have the query "SELECT * FROM <table-name> ORDER BY column1", the Jet Engine will cut the query down to "SELECT column2 FROM <table-name> ORDER BY column1" if column1 does not have an index defined on it and column2 does. The resulting query does not have the ORDER BY column (column1) in the SELECT list, which causes the error in Informix. NOTE: This situation will only happen when the particular ORDER BY column does not have an index defined on it. Workarounds: There are currently three workarounds to relieve this problem in the Jet Engine. 1. The newest and best workaround is an actual WorkArounds setting in the driver. This new workaround permits the use of queries in which the ORDER BY columns are not in the SELECT list. To enable this workaround add the "WorkArounds=1073741824" line to the data source you're using in either ODBC.INI or in the ODBC portion of the registry, as seen below: [Informix5 datasource] Driver=C:\\WINDOWS\\SYSTEM\\QEINF507.DLL . . . WorkArounds=1073741824 2. The second is to create an index on the ORDER BY column. This will allow the Engine to create its keyset with the indexed ORDER BY column. There is a bit of complexity to this option. If a primary key or unique index exists, the ORDER BY column must be the column or one of the columns in the primary key. If there is no primary key then the ORDER BY column has to be the column or columns in a non-unique index. 3. The last is to use the SQL Passthrough option for the Jet Engine. In Access, this involves choosing the SQL Specific menu and choosing the SQL Passthrough option. In Visual Basic, it involves adding the option 64 in your CreateDynaset: SET DS = DB.CreateDynaset(QueryString, 64) These passthrough options allow the query to bypass the Jet Engine and deliver the untouched query to the driver, thus avoiding the keyset building and the problem. The problem should be fixed when Microsoft releases an ODBC 2.0 compliant Jet Engine. Currently, however, with the 2.5 Jet Engine, the problem still exists." I tried the WorkArounds in a 16bit environment (MS Access 2.0 + 16bit CLI 2.5) and IT WORKED. I tried this trick in a 32bit environment (MS Access 97 + 32bit CLI 2.5) and it DIDN'T WORK (I tried both in the ODBC.INI and in the registry). In article <35F8CBB5.3969@em.uni-frankfurt.de>, Klaus Gotthardt <k.gotthardt@em.uni-frankfurt.de> wrote: > I tried using ms-access(97) as a frontend to informix(7.23,cli 2.5). It > worked, but i can sort only fields which are primary keys. > > (error: ....order by column (...) must be in select list (# -309)) > > -- > Klaus Gotthardt, > Johann Wolfgang Goethe-Universit't,Verwaltungsdatenverarbeitung > Email: K.Gotthardt@em.uni-frankfurt.de > Tel:+49 (0)69-798-23380 > Fax:+49 (0)69-798-23381 > Home,pgp-key: http://www.rz.uni-frankfurt.de/~gotthard > In article <35F8CBB5.3969@em.uni-frankfurt.de>, Klaus Gotthardt <k.gotthardt@em.uni-frankfurt.de> wrote: > I tried using ms-access(97) as a frontend to informix(7.23,cli 2.5). It > worked, but i can sort only fields which are primary keys. > > (error: ....order by column (...) must be in select list (# -309)) > > -- > Klaus Gotthardt, > Johann Wolfgang Goethe-Universit't,Verwaltungsdatenverarbeitung > Email: K.Gotthardt@em.uni-frankfurt.de > Tel:+49 (0)69-798-23380 > Fax:+49 (0)69-798-23381 > Home,pgp-key: http://www.rz.uni-frankfurt.de/~gotthard > -----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum