Thanks again for the help - it's invaluable to the oracle noob! :D I'm actually building an analysis system so that my boss can view some statistical subset of the information based on a few months or weeks. One part of the data is stored in *our* MySQL database (which our department control), whilst the rest of it is stored in the IT's oracle system. I have to pull a bunch of data from the MySQL database (usually with a date-based search parameter), then search the oracle system for the rest of the data for each return in the MySQL rows (by membership).
My current solution (for those on similar paths): *rows=db(db.my_table.created_on > date_one).select() * *ml=[] * *for row in rows: * * if row.my_field==my_criteria: * * ml.append(row.membership) * *rows=odb(odb.other_table.membership.belongs(ml)).select()* Of course, I have to worry about the length of the list that I'm passing, as I read that it cannot exceed 1000 items, but this shouldn't be an issue in normal use, and I'll wrap it in a test first. More efficient methods welcome!!! Martin On 6 April 2013 01:11, Niphlod <[email protected]> wrote: > > > On Friday, April 5, 2013 11:58:37 PM UTC+2, Martin Barnard wrote: >> >> Thanks for the info, Niphlod. >> >> I will look into the efficiency of the IN clause for my needs, as it >> appears to offer a solution which may mollify the IT DBA, and his demands >> for bind vars (they are concerned that a looped select will bring the db to >> it's knees). >> >> > .... a db(whatever.belongs(a_set)) issues ONE query only. > > If you want to "force" a looping query, you should do explicitely with > > mems = [1,2,3,4,5,6,...] > for c in mems: > one_result = db(db.table.field == c).select() > ...... > of course, for zillions values into mems, it's not a smart move. > > the smartest move with a huge set (i.e. the technique with most of the > "balance") would be "paginating" through your "mems" . > > You'd loop a few times but if you have thousands of values into "mems", a > single IN () (or thousands ORs) will take some time .... > > Try to "draw a limit" with your DBA and if he says that you're "allowed" > to do an IN() with 500 values at a times, you have it covered ^_^ > > On the other end, you have a requirement..... fetch a zillions rows..... > either you do it in one shot or in zillions/500 each. > > -- > > --- > You received this message because you are subscribed to a topic in the > Google Groups "web2py-users" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/web2py/E2pVWl_71t4/unsubscribe?hl=en. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > For more options, visit https://groups.google.com/groups/opt_out. > > > -- --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. For more options, visit https://groups.google.com/groups/opt_out.
