Informix + Access SQL + Querys

INFORMIX-SQL Version 6.05.UD1 I have the following query from my access database. SELECT informix_opdetm.list_price, informix_opheadm.address1, informix_opdetm.product, informix_opheadm.territory, informix_opheadm.region, informix_opdetm.order_qty,informix_opheadm.date_entered FROM informix_opdetm INNER JOIN informix_opheadm ON informix_opdetm.order_no = informix_opheadm.order_no WHERE (((informix_opdetm.list_price) Like [Enter Price]) AND ((informix_opdetm.product) Like [Please Enter Product]) AND ((informix_opheadm.date_entered) Between [Enter from date] And [Enter to date])); How hard would it be for me to convert this into a Informix Report/query. The reason is that I am using a ODBC driver linking into the Informix system over a 10base/T connection and having over 60,000 records to search through, I often get an ODBC call fail message. Is this the driver I am using is called tun32 by a company called esker! Is this the problem? Are there better drivers to help speed things up and make things a bit more stable? I am abit of a Informix/SQL virgin, so any help or guidance would be great. Thanks for your time. Carl email: info@hamilton-acorn.co.uk

Carl Grant wrote: > > INFORMIX-SQL Version 6.05.UD1 > > I have the following query from my access database. > > SELECT informix_opdetm.list_price, informix_opheadm.address1, > informix_opdetm.product, informix_opheadm.territory, > informix_opheadm.region, informix_opdetm.order_qty,> informix_opheadm.date_entered > FROM informix_opdetm INNER JOIN informix_opheadm ON informix_opdetm.order_no > = informix_opheadm.order_no > WHERE (((informix_opdetm.list_price) Like [Enter Price]) AND > ((informix_opdetm.product) Like [Please Enter Product]) AND > ((informix_opheadm.date_entered) Between [Enter from date] And [Enter to > date])); > > How hard would it be for me to convert this into a Informix > Report/query. With current products, you'd have to do a little rewriting; the INNER JOIN notation is not supported. It should be in the Centaur products out later this year. However, it is not hard to do the rewrite: SELECT D.list_price, H.address1, D.product, H.territory, H.region, D.order_qty, H.date_entered FROM informix_opdetm AS D, informix_opheadm AS H WHERE D.order_no = H.order_no AND (((D.list_price) LIKE [Enter Price]) AND ((D.product) LIKE [Please Enter Product]) AND ((H.date_entered) BETWEEN [From date] AND [To date])); I used the table aliases to make it shorter. You have to decide how the bits in square brackets will be handled - you probably get the data from the user somehow and insert it into the query, but the details of how depend on the language you use. > The reason is that I am using a ODBC driver linking into the > Informix system over a 10base/T connection and having over 60,000 > records to search through, I often get an ODBC call fail message. > Is this the driver I am using is called tun32 by a company called > esker! Is this the problem? Are there better drivers to help speed > things up and make things a bit more stable? I don't know why there'd be trouble -- the query should be run by the database without problem, and the call fail problem suggests a communications problem. But I'm doing some WAGging here - treat with extreme caution. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Informix CLI SDK 2.10 bundles the Intersolv 3.11 ODBC driver, which works great for us. Download from www.informix.com In article <926677939.209644@europa.epinet.co.uk>, "Carl Grant" <mrcarlgrant@hotmail.com> wrote: > INFORMIX-SQL Version 6.05.UD1 > > I have the following query from my access database. > > SELECT informix_opdetm.list_price, informix_opheadm.address1, > informix_opdetm.product, informix_opheadm.territory, > informix_opheadm.region, informix_opdetm.order_qty,> informix_opheadm.date_entered > FROM informix_opdetm INNER JOIN informix_opheadm ON informix_opdetm.order_no > = informix_opheadm.order_no > WHERE (((informix_opdetm.list_price) Like [Enter Price]) AND > ((informix_opdetm.product) Like [Please Enter Product]) AND > ((informix_opheadm.date_entered) Between [Enter from date] And [Enter to > date])); > > How hard would it be for me to convert this into a Informix Report/query. > The reason is that I am using a ODBC driver linking into the Informix system > over a 10base/T connection and having over 60,000 records to search through, > I often get an ODBC call fail message. Is this the driver I am using is > called tun32 by a company called esker! Is this the problem? Are there > better drivers to help speed things up and make things a bit more stable? > I am abit of a Informix/SQL virgin, so any help or guidance would be great. > Thanks for your time. Carl email: > info@hamilton-acorn.co.uk > > --== Sent via Deja.com http://www.deja.com/ ==-- ---Share what you know. Learn what you don't.---