You can file a ticket for this but note that not everything is possible with use_ansi=False. that mode of operation is somewhat miraculous that it even exists. this may be a trivial issue but I cant assert that until I've had time to study it.
On May 3, 2010, at 8:47 AM, Kent wrote: > 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. > -- 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.