Re: MS-Access: can't sort linked INFORMIX table?
This is getting somewhat out of hand. Informix requires, at least for any version of the engine I'm familiar with (and that would be a lot) that the order by column be in the select list. Not an unusual requirement for any database engine. Indexes may or may not alter the order of the output, but to do this you would have to specify an index to use in the query using a 7.30 engine. And then the output order will not be guaranteed. No index is ever required for an order by clause. It just often times speeds things up, eliminating the need for a sort and temp tables. In short,, this is not an odbc issue, just a bug in your SQL coupled with not reading the error messages correctly. HTH, David larry.linson@ntpcug.org on 03/21/99 04:07:32 PM Please respond to larry.linson@ntpcug.org To: informix-list@iiug.org cc: (bcc: David Coburn/ISG/WVUS/WorldVision) Subject: Re: MS-Access: can't sort linked INFORMIX table? pmpg_No Spam_98@Hotmail.com (Pedro Gil ) wrote: > But think that the problem reside in the ODBC driver or CLI can > decide. > > What i can say too you more than you allready explain, is that if you > don't have the index created in the Informix, you will not be able to > sort that field in any query or table. > > The turn around that problem for me was, to create Query Pass-through > that way you can retrive data sorted that doesn't have indexes created > in the INFORMIX. Obviously, one could get the Informix DBA to create an index on the field or create one yourself if you do Informix DBA work. An appropriate passthrough query should solve the problem. Another solution would be to create a View in Informix with the appropriate ORDER BY clause. As I understand it, this is a problem with the way Jet handles the queries it sends to the server (Informix) -- they differ from what you see in Access, even in SQL view. I don't think InterSolv is going to be able to solve it for you. And, of course, as you point out, you get the erroneous message even though the field _IS_ in your select list. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
See my comments and new info below. In article <7d47co$h7k$1@news.xmission.com>, "David Coburn" <dcoburn@worldvision.org> wrote: > > This is getting somewhat out of hand. Informix requires, at least for any > version of the engine I'm familiar with (and that would be a lot) that the > order by column be in the select list. Not an unusual requirement for any > database engine. Indexes may or may not alter the order of the output, but > to do this you would have to specify an index to use in the query using a > 7.30 engine. And then the output order will not be guaranteed. > > No index is ever required for an order by clause. It just often times > speeds things up, eliminating the need for a sort and temp tables. > > In short,, this is not an odbc issue, just a bug in your SQL coupled with > not reading the error messages correctly. Perhaps, I did not make this clear. When I said "sort" in my origianl message I was not writing any SQL at all. I was using either MS-Access "sort asscending" pull down directly in the table view. So this is not "My" SQL at all it's someone in Redmonds. Further more, I turned on ODBC tracing with the following results: Begin snipet of SQL.log ..... MSACCESS fff9f12f:fff9fd47 EXIT SQLExecDirect with return code -1 (SQL_ERROR) HSTMT 0x0257107c UCHAR * 0x1e533d60 [ -3] "SELECT y2k updatable. recser FROM mfalcian.items y2k updatable ORDER BY dept " ..... MSACCESS fff9f12f:fff9fd47 EXIT SQLErrorW with return code 0 (SQL _SUCCESS) HENV 0x026807a0 HDBC 0x02687520 HSTMT 0x0257107c WCHAR * 0x0062d7bc (NYI) SDWORD * 0x0062dc28 (-201) WCHAR * 0x0062d7c8 [ 140] "[Informix][Odbc Infor mix Driver][Informix]A syntax error has occurred." End snipet of SQL.LOG > > HTH, > > David > > larry.linson@ntpcug.org on 03/21/99 04:07:32 PM > > Please respond to larry.linson@ntpcug.org > > To: informix-list@iiug.org > cc: (bcc: David Coburn/ISG/WVUS/WorldVision) > Subject: Re: MS-Access: can't sort linked INFORMIX table? > > pmpg_No Spam_98@Hotmail.com (Pedro Gil ) wrote: > > But think that the problem reside in the ODBC driver or CLI can > > decide. > > > > What i can say too you more than you allready explain, is that if you > > don't have the index created in the Informix, you will not be able to > > sort that field in any query or table. > > > > The turn around that problem for me was, to create Query Pass-through > > that way you can retrive data sorted that doesn't have indexes created > > in the INFORMIX. > > Obviously, one could get the Informix DBA to create an index on the field > or > create one yourself if you do Informix DBA work. An appropriate passthrough > query should solve the problem. Another solution would be to create a View > in > Informix with the appropriate ORDER BY clause. > > As I understand it, this is a problem with the way Jet handles the queries > it > sends to the server (Informix) -- they differ from what you see in Access, > even in SQL view. I don't think InterSolv is going to be able to solve it > for > you. And, of course, as you point out, you get the erroneous message even > though the field _IS_ in your select list. > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own > > Mike Falciani Axiom Inc -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own