Re: Informix/Access Sorting Bug?

Irwin Goldstein wrote: > Curt Van Den Heuvel <heuvelc@primenet.com> wrote in article > <6aidih$quc@nntp02.primenet.com>... > > I have an Access-97 database which contains several linked informix 7.23 > > tables (using Informx CLI-32 to connect). If I try to order by an column > > other than the primary key, I get the following error from the ODBC > driver > > : "COLUMN whatever MUST BE IN ORDER BY LIST". I put a trace on the ODBC > > driver, and indeed it was trying to order by the specified column without > > having it in the SELECT list. > > > > Question: Does the SQL command originate with Access, Informix-CLI or the > > ODBC driver? > > <snip> > > I belive the problem lies with Access. The OpenLink ODBC drivers try to > intelligently handle this by adding the needed column(s) to the select > list. Sometimes it works, sometime it does not. (The latest version from > OpenLink may do this even more reliably.) Sometimes, even if you include > the ordered by column as displayed in your query, Access may still return > this error. This is because Access will usually try to fetch just the > primary key of each table in the query, and then ftech the other columns > only as they are needed for display. A way around this is to change the > query's recordset type to "snapshot" rather than "dynaset". This will > cause Access to send the whole query as a single SQL statement much like > you would expect. This is fine if your query is primarily to be used for a > report or other "read-only" purposes. Data can not be updated through a > "snapshot". Well put. We see the "order by column must be in select list" from time to time and have it covered. Try our ODBC driver: SCO SQL-Retriever. Take a look at http://www.sco.com/vision/products/sqlretriever/ for more information and a downloadable eval. Allan Gould (allang at sco dot com) (Please remove anti-spam measures if replying)