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.
