Re: multi-statement prepare

I don't think this is a legal syntax. When dbaccess or any other front-end tool gets a "double statement" command, it actually generates two dynamic queries. The order of items within the statement is "order independent" in that the optimizer will re-order the "where" clauses using mathametical rules of association, negation, etc. For instance in the following query select a.a, b.b, c.c from a, b, c where a.d = b.d and b.d = c.d and a.d > 100 a simple rules based optimizer would be forced to select table a first because of the a.d > 100. it would then join that subset with table b, and finally with table c. What the Informix optimizer would do is to decide, if a.d = b.d = c.d, then it is also true that the filter could also be a.d > 100 or b.d > 100 or c.d > 100. if c.d > 100 produced the smallest subset of rows, then the optimizer would dynamically convert the query to select a.a, b.b, c.c from a, b, c where a.d > 100 and a.d = b.d and a.d = c.dI think that is what is really meant by "order independent" What you are trying to do is to create a single query from two queries. It's really not the same thing. Can anyone else confirm this? Thanks --------------------------------------------------------------- Subject: multi-statement prepare From: Douglas Wilson <dgwilson@gte.net> Date: Sat, 15 Mar 1997 09:20:02 -0800 Message-ID: <332ADA42.4455@gte.net> I recieved a syntax error on something similar to the following sort of prepare: let sql_stmt= "update tbl1 set (col1, col2)= ", "(col1+1, (col3/col4)*100) ", "where col2=? and col4!=0; ", "update tbl1 set (col1, col2)= ", "(col1+1, (col3/20)*100) ", "where col2=? and (col4=0 or col4 is null); ", "update tbl2 set (col1)=(col1+1) where col2=?;" prepare sql_id from sql_stmt I understand that you're supposed to think of the statements as a unit which get executed in no particular order (even though I found an example in the manual which drops a table then creates one with the same name), therefore you could get into trouble updating the same table twice in the same statement, but this example updates mutually exclusive rows in tbl1. Has anyone else ever experienced this? Apparently I'm the first to report this. Any opinions on whether or not it's a real problem? Douglas Wilson Madison Pruet