Re: TEMP table comes into exist very slow

Thomas wrote: > "Mark D. Stock" <mdstock@mydassolutions.com> wrote in message news:<bf1qof$heu$1@terabinaries.xmission.com>... > >>Thomas wrote: >> >>>I have a piece of code like this >>>SELECT SUM(col1) c1 >>>>From tbl GROUP BY col2 >>>INTO TEMP tmp_tbl WITH NO LOG; >>> >>>Select AVG(c1) FROM tmp_tbl; >>> >>>My code access an IDS9.4 on Solaris through ADO, the driver is OleDB >>>from IBM Informix Connect. If I run it in one clause, some like >>>dbConnection.Execute(query), I will get an error message: Table >>>tmp_tbl doesn't exist. I have to seperate the statement into 2 >>>Execument statement. >>> >>>Also if I DROP the temp table explicitly after the 2nd SELECT, I will >>>get an error says the TEMP table cannot be dropped. More >>>interestingly, if I run my code twice continusously, there is an >>>error. I have to wait a few seconds before I can click the Refresh >>>button of my browser. >>> >>>Anyone knows why? >> >>No, not really, but I guess the entire SQL is being parsed before it is >>being executed, hence the missing table. But what are you trying to achieve? >> >>The SUM(col1) will return a single row into your temp table tmp_tb1. The >>average of a single value is that value, so the second select is >>redundant in your example, AFAICS. > > Mark, > > With the GROUP BY clause on different column, the result of SUM will > be multiple rows. I need to do get some other statistics data from > them. Oh yes. Silly me for not spotting the different column name. In that case, I guess you need to run them as 2 statements, as you found. I am not familiar with ADO or OleDB, so I assume a statement is parsed before execution and it can't handle temp objects. Maybe there is a function other than Execute() that will execute deferred? Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list