On Tue, 22 May 2007 17:33:27 -0500, Carl Karsten wrote
> Or some such abomination of results of one query as a parameter of a
> 2nd.
>
> given my use case, I can understand why this isn't in the spec, and
> why it may never be. but it seems to come up more often that I
> would expect, so here we go.
>
> My current problem: reconcile transaction details that are off due
> to rounding errors. the 2 sets of details are stored on different
> servers, and no chance of getting one server to hit the 2nd, so the
> python client code is going to have to help by getting a list of
> keys from one and constructing "WHERE OtherKey IN ( 'key1', 'key2',
> 'key3', ...)" which isn't 'hard' but I find annoying that I have
> to convert formats in the application layer.
Option 1: Create a temporary table on one server and load the data from the
other server into it. Then, use a server side subquery or join the tables
together.
If you don't have write permission on either server, there is Option 2: Fetch
all relevant data from server 1 into client memory, fetch all relevant data
from server 2 into client memory, and do the reconciliation in client memory.
There is also Option 3: Use actual parameter passing to build a WHERE ... IN
(...) clause:
cSql = ("select ktbl2_fk from tbl3 where OtherKey IN ("
+",".join("%s" for _ in cList)
+")" )
cur.execute(cSql, cList)
HTH,
--
Carsten Haese
http://informixdb.sourceforge.net
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
