[DB-SIG] client side sub queries

[Carl Karsten]

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.

I have no idea how this should be implemented.  I can imagine something like this:

cSql="select ktbl1_pk from tbl1 where cFid1 = %(id)s"
cur1.execute( cSql, { 'id':'a' } )
rows1 = cur.fetchall()
cSql = "select ktbl2_fk from tbl3 where ktbl1_fk IN %l"
cur2.execute( cSql, rows1 )

Or maybe even pass the whole cursor in:

cSql="select ktbl1_pk from tbl1 where cFid1 = %(id)s"
cur1.execute( cSql, { 'id':'a' } )
cSql = "select ktbl2_fk from tbl3 where ktbl1_fk IN %c"
cur2.execute( cSql, cur )


In case it isn't clear what I am trying to do, below is working code
including the CREATES. (which actually have more tables than are used by what I 
posted - cuz my over all task is even worse.)

Carl K

# get first set
cSql="select ktbl1_pk from tbl1 where cFid1 = %(id)s"
cur.execute( cSql, { 'id':'a' } )
rows = cur.fetchall()

# get 2nd based on first.
list = ["'%s'" % x for x in rows]
cList = ','.join( list )
cSqlWhere = "ktbl1_fk IN (%s)" % cList
cSql = "select ktbl2_fk from tbl3 where %s" % cSqlWhere
print cSql
# select ktbl2_fk from tbl3 where ktbl1_fk IN ('1','2','3')
cur.execute( cSql )
rows = cur.fetchall()


# mkTestdb.sql

# tbl1 and tbl2 hold monies that should be equal for one transaction
# tbl3 is the join table
# tbl1.cFid1 is the transaction ID.

drop database testdb1;

create database testdb1;

grant all on testdb1.* to testUserA IDENTIFIED BY 'pw';

create table testdb1.tbl1 (
         ktbl1_pk int auto_increment primary key,
         cFid1 char(10), nFid2 decimal(10,2) );

create table testdb1.tbl2 (
         ktbl2_pk int auto_increment primary key,
         nFid2 decimal(10,2) );

create table testdb1.tbl3 (
         ktbl3_pk int auto_increment primary key,
         ktbl1_fk int references tbl1,
         ktbl2_fk int references tbl2);

# sample data:
# trasaction #a
# t1 (1.01, 1.02, 1.03)
# t2 (1.01, 1.02, 1.04)
# but not stored in the same order
# (will work on exactly how to deal with that)
# looks like this will work for Oracle:
#  ORDER BY decode( X, n1, 1, n2, 2, n3, 3...)

insert into testdb1.tbl1 (ktbl1_pk, cFid1, nfid2) values (1, 'a', 1.01) ;
insert into testdb1.tbl1 (ktbl1_pk, cFid1, nfid2) values (2, 'a', 1.02) ;
insert into testdb1.tbl1 (ktbl1_pk, cFid1, nfid2) values (3, 'a', 1.03) ;

insert into testdb1.tbl2 (ktbl2_pk, nfid2) values (1, 1.01) ;
insert into testdb1.tbl2 (ktbl2_pk, nfid2) values (2, 1.02) ;
insert into testdb1.tbl2 (ktbl2_pk, nfid2) values (3, 1.04) ;

insert into testdb1.tbl3 (ktbl1_fk,ktbl2_fk) values (1, 1) ;
insert into testdb1.tbl3 (ktbl1_fk,ktbl2_fk) values (3, 3) ;
insert into testdb1.tbl3 (ktbl1_fk,ktbl2_fk) values (2, 2) ;

Carl K