Given the table below I am trying to get the where statement built using just core. I have tried a number of thing and am not able to get the query built.
I can get the query close but not correct. I must be missing something simple. The database is Mysql and SQLAchemy is 9.7 The query does work correct when used as text statement. CREATE TABLE `trs_transaction` ( `transaction_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `account` bigint(20) unsigned DEFAULT NULL, `cost_center` bigint(20) unsigned NOT NULL, `invoice_date` date NOT NULL, `trust_status` enum('open','closed') NOT NULL, `interest` decimal(16,2) DEFAULT NULL, `balance` decimal(16,2) DEFAULT NULL, `current_balance` decimal(16,2) DEFAULT NULL, PRIMARY KEY (`transaction_id`), UNIQUE KEY `invoice_date` (`invoice_date`,`account`,`cost_center`), KEY `account` (`account`), KEY `cost_center` (`cost_center`), ) ENGINE=InnoDB; where = "select m.account, m.invoice_date, m.trust_status, " \ "m.interest, m.cost_center, m.current_balance from \n" \ "(select max(invoice_date) as max_invoice_date, account, "\ "cost_center from trs_transaction where cost_center = %s and " \ " invoice_date <= '%s' group by account) as x \n" \ "inner join trs_transaction as m on \n" \ "x.max_invoice_date = m.invoice_date and x.account = m.account " \ "and m.cost_center = x.cost_center and " \ "m.current_balance > 0" % (self.cost_center, self.invoice_date) -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.