if you want to send (simplified, succinct) table defs i can show you how to get that in core.
On Sep 15, 2014, at 6:39 PM, Mike Bernson <[email protected]> wrote: > 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 [email protected]. > To post to this group, send email to [email protected]. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
