I am working on a chemical database under Oracle 9i and SQLAlchemy 0.4.6, and I'm running into a problem.
I am creating a rather complex query in which I need the ability to constrain that query on the basis of a query to a very specialized view. I am doing so because chemical compounds are hashed in Oracle via a 'cartridge' technology that provides very specific functions on which to query them. The syntax of the query against the view looks like this: SELECT COMPOUND_ID FROM MOLECULE_VIEW MV WHERE FLEXMATCH(MV.CTAB, :chimeString, 'all')=1; Without going into excessive and boring details, flexmatch() is a specialized function which compares a molecule or a fragment of a molecule to a chemical table. I want to be able to join the results of this query to a generative select against my Compound table. So, a very simpified version of my query would look something like this: query = compound.select() query = query.where(compound.c.username=='some_user') query = query.where(compound.c.molweight >= 500) Ultimately, I want join on the structure search, producing SQL that looks like: SELECT C.* FROM COMPOUND C WHERE C.USERNAME = 'some_user' AND C.MOLWEIGHT >= 500 AND C.COMPOUND_ID IN (SELECT DISTINCT COMPOUND_ID FROM MOLECULE_VIEW MV WHERE FLEXMATCH(MV.CTAB, ':chimeString', 'all')=1 I had initially thought I could create a 'pseudo-table' object out of the view, but this does not work well with Oracle, whether autoload is set to true or false. Any suggestions you can offer would be very much appreciated! Thanks, Joann --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
