Need Help with Z SQL Method

--===============5438774380669207350==
Content-Type: multipart/alternative; boundary=00c09f93d641bead0304875f4c90

--00c09f93d641bead0304875f4c90
Content-Type: text/plain; charset=ISO-8859-1

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

--00c09f93d641bead0304875f4c90
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. <br><br>I =
am having a problem with more complex queries:<br>SELECT
playerID, sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) AS AB FROM
offense WHERE gameID=3D&lt;dtml-sqlvar gameID type=3Dint&gt; returns the
error <br>
OperationalError: (1140, &#39;Mixing of GROUP columns
(MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is
no GROUP BY clause&#39;)<br><br>When I try the same query on the mysql data=
base (ie not through the ZMI and Z SQL Method), it works just fine.<br><br>=
The same query without the playerID works from both the ZMI and in mysql.<b=
r>

SELECT sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt) AS AB FROM
offense WHERE gameID=3D&lt;dtml-sqlvar gameID type=3Dint&gt;<br><br>I looke=
d in the Zope book and read the section on Relational Database Connectivity=
, so I tried<br>SELECT playerID, sum(PA) - sum(BB) - sum(HBP) - sum(SacBunt=
) AS AB FROM offense<br>

&lt;dtml-sqlgroup where&gt;<br>=A0=A0 gameID=3D&lt;dtml-sqlvar gameID type=
=3Dint&gt;<br>&lt;/dtml-sqlgroup&gt;<br><br>and I get the same error. <br><=
br>So how do I translate the query<br>select playerID, sum(PA) - sum(BB) - =
sum(HBP) - sum(SacBunt) as AB from offense<br>

where gameID=3D&lt;an input value&gt; <br><br>into something that Z AQL Met=
hod understands?<br><br>Thanks!<br><font color=3D"#888888"><br>Mark<br></fo=
nt>

--00c09f93d641bead0304875f4c90--

--===============5438774380669207350==
Content-Type: text/plain; charset="us-ascii"
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

_______________________________________________
Zope-DB mailing list
[email protected]
https://mail.zope.org/mailman/listinfo/zope-db

--===============5438774380669207350==--