The following script works as expected (also in 0.5.8) with use_ansi=True. However, with use_ansi=False, the SQL is ill-formed:
=============================================================== from sqlalchemy import * from sqlalchemy.orm import * engine = create_engine('oracle://arc:a...@localhost:1521/xe? use_ansi=False',echo=True) metadata = MetaData() Session = sessionmaker(bind=engine) session = Session() orders_table = Table("orders", metadata, Column("orderid", Unicode, primary_key=True) ) orderdetails_table = Table("orderdetails",metadata, Column("orderid", Unicode, ForeignKey('orders.orderid'), primary_key=True), Column("lineid", Integer, primary_key=True), Column("saleprice", Numeric, nullable=False), Column("qtyordered",Numeric) ) class Order(object): pass class OrderDetail(object): pass order_mapper = mapper(Order, orders_table, properties=dict(orderdetails=relation(OrderDetail, cascade='all,delete-orphan', single_parent=True, lazy=False, backref=backref('parentorder', cascade='refresh-expire,expunge')))) # ----------- totalsale ----------- # note, I needed to add aliases because if you join with these tables in the rest of the query, # we need it to be un-ambiguous od_alias=orderdetails_table.alias('od__a') order_mapper.add_property('totalsale', # totalsale is an inline view column column_property( select([func.sum(od_alias.c.qtyordered * od_alias.c.saleprice)], orders_table.c.orderid==od_alias.c.orderid ).label('totalsale'))) orderdetail_mapper = mapper(OrderDetail, orderdetails_table) #metadata.create_all(engine) o=session.query(Order).all() =============================================================== Expected SQL (use_ansi=True): ------------------------------------------- 2010-04-30 21:09:10,359 INFO sqlalchemy.engine.base.Engine.0x...9450 SELECT orders.orderid AS orders_orderid, (SELECT sum(od__a.qtyordered * od__a.saleprice) AS sum_1 FROM orderdetails od__a WHERE orders.orderid = od__a.orderid) AS totalsale, orderdetails_1.orderid AS orderdetails_1_orderid, orderdetails_1.lineid AS orderdetails_1_lineid, orderdetails_1.saleprice AS orderdetails_1_saleprice, orderdetails_1.qtyordered AS orderdetails_1_qtyordered FROM orders LEFT OUTER JOIN orderdetails orderdetails_1 ON orders.orderid = orderdetails_1.orderid 2010-04-30 21:09:10,360 INFO sqlalchemy.engine.base.Engine.0x...9450 {} SQL with use_ansi=False: ------------------------------------------ sqlalchemy.exc.DatabaseError: (DatabaseError) ORA-00936: missing expression 'SELECT orders.orderid AS orders_orderid, (SELECT sum(od__a.qtyordered * od__a.saleprice) AS sum_1 \nFROM orderdetails od__a \nWHERE orders.orderid = od__a.orderid AND ) AS totalsale, orderdetails_1.orderid AS orderdetails_1_orderid, orderdetails_1.lineid AS orderdetails_1_lineid, orderdetails_1.saleprice AS orderdetails_1_saleprice, orderdetails_1.qtyordered AS orderdetails_1_qtyordered \nFROM orders, orderdetails orderdetails_1 \nWHERE orders.orderid = orderdetails_1.orderid(+)' {} Note the " AND )" before "AS totalsale" is causing the missing expression database error. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@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.