[Zope-DB] Need Help with Z SQL Method

Mark Phillips mark at phillipsmarketing.biz
Mon May 24 19:21:25 EDT 2010


I am using Plone 2.5.5, CMF-1.6.4, Zope (Zope 2.9.8-final, python 2.4.4,
linux2), Five 1.4.4, Python 2.4.4 (#1, Jun 16 2009, 12:19:17) [GCC 4.3.2],
PIL 1.1.6 and MySQL 5.0. I have installed the Zope MySQL adapter and can
write successful simple queries to the database.

I am having a problem with more complex queries:
SELECT playerID, sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) AS AB FROM
offense WHERE gameID=<dtml-sqlvar gameID type=int> returns the error
OperationalError: (1140, 'Mixing of GROUP columns (MIN(),MAX(),COUNT(),...)
with no GROUP columns is illegal if there is no GROUP BY clause')

When I try the same query on the mysql database (ie not through the ZMI and
Z SQL Method), it works just fine.

The same query without the playerID works from both the ZMI and in mysql.
SELECT sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) AS AB FROM offense WHERE
gameID=<dtml-sqlvar gameID type=int>

I looked in the Zope book and read the section on Relational Database
Connectivity, so I tried
SELECT playerID, sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) AS AB FROM
offense
<dtml-sqlgroup where>
   gameID=<dtml-sqlvar gameID type=int>
</dtml-sqlgroup>

and I get the same error.

So how do I translate the query
select playerID, sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) as AB from
offense
where gameID=<an input value>

into something that Z AQL Method understands?

Thanks!

Mark
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mail.zope.org/pipermail/zope-db/attachments/20100524/844039f5/attachment.html 


More information about the Zope-DB mailing list