<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=windows-1250">
<META NAME="Generator" CONTENT="MS Exchange Server version 6.5.7638.1">
<TITLE>RE: [Zope-DB] ZSQL </TITLE>
</HEAD>
<BODY>
<!-- Converted from text/plain format -->
<P><FONT SIZE=2>Thanks for the reply. This should get me pointed down a path. </FONT>
</P>
<P><FONT SIZE=2>FYI</FONT>
<BR><FONT SIZE=2>I'm connecting to AS400 DB2 via ODBC.</FONT>
</P>
<P><FONT SIZE=2>Thanks again.</FONT>
<BR><FONT SIZE=2>Israel</FONT>
</P>
<BR>
<P><FONT SIZE=2>-----Original Message-----</FONT>
<BR><FONT SIZE=2>From: zope-db-bounces@zope.org [<A HREF="mailto:zope-db-bounces@zope.org">mailto:zope-db-bounces@zope.org</A>]On</FONT>
<BR><FONT SIZE=2>Behalf Of Dieter Fischer</FONT>
<BR><FONT SIZE=2>Sent: Monday, April 17, 2006 5:57 PM</FONT>
<BR><FONT SIZE=2>To: zope-db@zope.org</FONT>
<BR><FONT SIZE=2>Subject: RE: [Zope-DB] ZSQL </FONT>
</P>
<BR>
<P><FONT SIZE=2>Hello</FONT>
</P>
<P><FONT SIZE=2>This is a SQL problem. So it depends what kind of DB you're using. In Oracle</FONT>
<BR><FONT SIZE=2>you could do something like this (copied from orafaq):</FONT>
</P>
<P><FONT SIZE=2>SELECT *</FONT>
<BR><FONT SIZE=2>FROM (</FONT>
<BR> <FONT SIZE=2>SELECT job,</FONT>
<BR> <FONT SIZE=2>sum(decode(deptno,10,sal)) DEPT10,</FONT>
<BR> <FONT SIZE=2>sum(decode(deptno,20,sal)) DEPT20,</FONT>
<BR> <FONT SIZE=2>sum(decode(deptno,30,sal)) DEPT30,</FONT>
<BR> <FONT SIZE=2>sum(decode(deptno,40,sal)) DEPT40</FONT>
<BR> <FONT SIZE=2>FROM scott.emp</FONT>
<BR> <FONT SIZE=2>GROUP BY job</FONT>
<BR> <FONT SIZE=2>)</FONT>
<BR><FONT SIZE=2>ORDER BY 1;</FONT>
</P>
<P><FONT SIZE=2>you can change it to your needs. But this doesn't help, when you have a</FONT>
<BR><FONT SIZE=2>variabe amount of rows (in your case transX). In this case you have to write</FONT>
<BR><FONT SIZE=2>something in PL/SQL (Oracle), plpgsql (PostgreSQL) or whatever your DB is</FONT>
<BR><FONT SIZE=2>programmed with, or write a Python Script in Zope (not fast, but portable).</FONT>
</P>
<P><FONT SIZE=2>Regards</FONT>
</P>
<P><FONT SIZE=2>Dieter</FONT>
</P>
<P><FONT SIZE=2>> -----Original Message-----</FONT>
<BR><FONT SIZE=2>> From: zope-db-bounces@zope.org</FONT>
<BR><FONT SIZE=2>> [<A HREF="mailto:zope-db-bounces@zope.org">mailto:zope-db-bounces@zope.org</A>]On Behalf Of Israel Carr</FONT>
<BR><FONT SIZE=2>> Sent: Monday, April 17, 2006 10:17 PM</FONT>
<BR><FONT SIZE=2>> To: zope-db@zope.org</FONT>
<BR><FONT SIZE=2>> Subject: [Zope-DB] ZSQL</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> I have a table with the columns:</FONT>
<BR><FONT SIZE=2>> id, username, trans_type</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> Sample records:</FONT>
<BR><FONT SIZE=2>> 1,user1,trans1</FONT>
<BR><FONT SIZE=2>> 2,user1,trans3</FONT>
<BR><FONT SIZE=2>> 3,user2,trans4</FONT>
<BR><FONT SIZE=2>> 4,user2,trans2</FONT>
<BR><FONT SIZE=2>> 5,user1,trans1</FONT>
<BR><FONT SIZE=2>> 6,user2,trans3</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> I would like to generate output that contains the username and the</FONT>
<BR><FONT SIZE=2>> total(count) number of each trans_type. Would also like to have a total</FONT>
<BR><FONT SIZE=2>> of all trans_type.</FONT>
<BR><FONT SIZE=2>> Roughly:</FONT>
<BR><FONT SIZE=2>> trans1 trans2 trans3 trans4</FONT>
<BR><FONT SIZE=2>> user1 2 0 1 0</FONT>
<BR><FONT SIZE=2>> user2 0 1 1 1</FONT>
<BR><FONT SIZE=2>> TOTAL 2 1 2 1</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> I have a ZSQL method with the following:</FONT>
<BR><FONT SIZE=2>> select COUNT(*)</FONT>
<BR><FONT SIZE=2>> from table</FONT>
<BR><FONT SIZE=2>> where trans_type = <dtml-sqlvar transtype type=string></FONT>
<BR><FONT SIZE=2>> and username = <dtml-sqlvar username type=string></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> When testing and feeding parameters it does give me the count for one</FONT>
<BR><FONT SIZE=2>> user and one transaction type.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> How can I create the desired output and have a general enough ZSQL</FONT>
<BR><FONT SIZE=2>> Method that I can feed these parameters? I've been playing with some</FONT>
<BR><FONT SIZE=2>> <dtml-in> tags and trying to call the ZSQL method but am not really sure</FONT>
<BR><FONT SIZE=2>> how to pass parameters or if I'm way out in left field. Would I need to</FONT>
<BR><FONT SIZE=2>> call the ZSQL method 8+ times to generate output like the table above?</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> I can do pretty basic form inputs into database and selects out based on</FONT>
<BR><FONT SIZE=2>> form or static values but this is a little more complex for me. Any</FONT>
<BR><FONT SIZE=2>> help would be appreciated.</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> Israel</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>> _______________________________________________</FONT>
<BR><FONT SIZE=2>> Zope-DB mailing list</FONT>
<BR><FONT SIZE=2>> Zope-DB@zope.org</FONT>
<BR><FONT SIZE=2>> <A HREF="http://mail.zope.org/mailman/listinfo/zope-db">http://mail.zope.org/mailman/listinfo/zope-db</A></FONT>
<BR><FONT SIZE=2>> --</FONT>
<BR><FONT SIZE=2>> No virus found in this incoming message.</FONT>
<BR><FONT SIZE=2>> Checked by AVG Free Edition.</FONT>
<BR><FONT SIZE=2>> Version: 7.1.385 / Virus Database: 268.4.2/314 - Release Date: 16.04.2006</FONT>
<BR><FONT SIZE=2>></FONT>
<BR><FONT SIZE=2>--</FONT>
<BR><FONT SIZE=2>No virus found in this outgoing message.</FONT>
<BR><FONT SIZE=2>Checked by AVG Free Edition.</FONT>
<BR><FONT SIZE=2>Version: 7.1.385 / Virus Database: 268.4.2/314 - Release Date: 16.04.2006</FONT>
</P>
<P><FONT SIZE=2>_______________________________________________</FONT>
<BR><FONT SIZE=2>Zope-DB mailing list</FONT>
<BR><FONT SIZE=2>Zope-DB@zope.org</FONT>
<BR><FONT SIZE=2><A HREF="http://mail.zope.org/mailman/listinfo/zope-db">http://mail.zope.org/mailman/listinfo/zope-db</A></FONT>
</P>
</BODY>
</HTML>