On Jan 26, 2011, at 11:46 AM, Pankaj wrote: > Hi, > > I have this subquery, which yields results > > sp_HeaderDetails = > session.query( InvoiceCashFlowPerDocNum.sequence_id, > InvoiceHeaderInfo.invoice_ref, InvoiceHeaderInfo.doc_num ) \ > .filter( ( InvoiceCashFlowPerDocNum.doc_num == > InvoiceHeaderInfo.doc_num ) ) \ > .subquery() > > I then do a outerjoin on another query > > cashflows = session.query( CashflowEventDetail, sp_HeaderDetails ) \ > .outerjoin( ( sp_HeaderDetails, > and_(sp_HeaderDetails.c.sequence_id == > CashflowEventDetail.sequence_id ) ) )
> > This above statement generates the following sql: > > > SELECT cash_flow_event_detail.id > FROM cash_flow_event_detail LEFT OUTER JOIN (SELECT > invoice_cashflows.sequence_id AS sequence_id, > invoice_header_information.invoice_ref AS invoice_ref, > invoice_header_information.doc_num AS doc_num > FROM invoice_cashflows, invoice_header_information > WHERE invoice_cashflows.doc_num = invoice_header_information.doc_num) > AS anon_1 ON anon_1.sequence_id = cash_flow_event_detail.sequence_id This doesn't make sense, your query has two different selectables passed to query() so should have at least all the columns from sp_HeaderDetails listed out in the columns clause, unless you're paraphrasing. The SQL is otherwise correct, unless you're on a platform such as Oracle which does not generate the "AS" keyword. SQLAlchemy does not generate "AS" when the statement is compiled against such a backend. If this is not the case, would need DB in use, version information, test case. -- 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.