Re: Utilizing temporary tables via an ODBC connection?

"William Fields" <Bill_Fields@azb.uscourts.gov> wrote in message news:d056kf$5ml$1@apollo.nyed.circ2.dcn... > Hello, > > We (my partner and I) had a situation come up yesterday that we were able to > get around, but I'd like input on what the best practice might be. > > I have a temporary resultset that I'd like to join with another SQL, or use > in a subquery in another SQL. FYI - I'm using Microsoft Visual FoxPro, > connecting to an IDS v7.3 instance via the current ODBC drivers from IBM. > > What we ran into was that our initial SQL ended up as a local FoxPro cursor, > which of course could not be referenced within another SQL statement sent > over the ODBC connection. The solution was to parse the cursor and assemble > a new SQL statement with an "WHERE IN (ValueList)" clause. > > Is there a way to create a temporary cursor on the Informix server that I > can reference in other SQL statements via an ODBC connection? Any > information would be helpful. > > Thanks. hard to be sure, I'm not 100% sure what you're getting at - but I'd think about one possibility. Have you considered writing a stored procedure (or view or nest of views) to return that data? OK - you don't really have the kind of optimisation for this you get in - for example SQLServer, but, as I'd assume that your server's probably rather more powerful than the clients it should be able to process the query faster. Also, particularly if the datasets are large, processing on the server and returning the resultset as one recordset rather than several over the network and back, with additional processing, may well improve performance (if that's the issue). If you do use a stored procedure, see if you can get by without cursors. hth Andrew