Informix CLI 2.8 Translation Options in NT ODBC

I am invoved in a project using informix queried via ADO from ASP and IIS through Informix CLI 2.8 ODBC (all on an NT Server) to informix residing on a Sun box. The query I want to get is: SELECT DISTINCT Group_Detail.GroupId, Group_Detail.ClientId, Group_Detail.TestId, Group_Detail.CenterId, Apntt_Date_Dt, Sponsor, NumAppt_DT, (SELECT COUNT(groupid) FROM appointment WHERE appointment.groupid = group_detail.groupid AND appointment.candid <> -1) AS Filled FROM Group_Detail ORDER BY sponsor This query has been run through dbaccess and it is indeed correct, valid, and returns the proper data. But what the ODBC driver is sending out to Informix is: SELECT * FROM (SELECT DISTINCT Group_Detail.GroupId, Group_Detail.ClientId, Group_Detail.TestId, Group_Detail.CenterId, Apntt_Date_Dt, Sponsor, NumAppt_DT, (SELECT COUNT(groupid) FROM appointment WHERE appointment.groupid = group_detail.groupid AND appointment.candid <> -1) AS Filled FROM Group_Detail) ORDER BY sponsor Which is obviously poor syntax and rightfully returns an error. The problem is that the ODBC driver feels a strange compulsion to manipulate my query. If the query is rewritten as: select distinct g.groupid, g.clientid, g.testid, g.centerid, g.apntt_date_dt, g.sponsor, g.numappt_dt, count(a.groupid) as filled from group_detail g, outer appointment a where a.groupid=g.groupid and a.candid=-1 group by g.groupid, g.clientid, g.testid, g.centerid, g.apntt_date_dt, g.sponsor, g.numappt_dt Everything works. But my question is how can I tell the ODBC driver to trust me and just send all my SQL through. Furthermore, what are the "Translation Options" in the driver for? Is it to tell it to not translate my SQL, or is it for international use? -Michael Hurwitz Developer Information Concepts, Inc. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

mhurwitz@my-dejanews.com schrieb in Nachricht <78056k$9m4$1@nnrp1.dejanews.com>... >But my question is how can I tell the ODBC driver to trust me and just send >all my SQL through? There is via way to set this in the program via the odbc-call SQLSetStmtOption or SQLSetConnectionOption to avoid reparsing the SQL. I don't no which of these functions will do that. ODBC commonly rewrites statements to change ODBC escape sequences to database specific requests ( e.g. {d '1999-18-01'} to a DB-specific date). In your case the driver rewrites it to a 9.x compatible form ( I think so at least) and thats probably a bug. Harald Ums