RE: [Zope] How do I create a cursor on a query in Zope
Sorry for the confusion, I'm trying to open a cursor on a query say: Open a cursor: select NAME from table Where I can read one name at a time and perform an action on that name. For example: select blah, count(*) as table2Count from table2 where table2.name = NAME group by blah select blah2, count(*) as table3Count from table3 where table3.name = NAME group by blah2 select blah3, count(*) as table4Count from table3 where table3.name = NAME group by blah3 Write a row(NAME, table2count, table3count, table4count) CLOSE THE CURSOR The end result would be something like: NAME Count of Visits Count of Purchases Count of Inquiries Joe 1234 4331 5965 Jane 3766 3534 35 Jeff 3457 6433 3653 Harry 5433 88 2854 Tim 56 2434 9865 . . . The capitalized NAME is equal to the value of the first response from Query1. Let me know if that is still confusing . . . Thanks again in advance, Quad -----Original Message----- From: Evan Simpson [mailto:evan@tokenexchange.com] Sent: Tuesday, August 24, 1999 3:34 PM To: Marten Quadland Subject: Re: [Zope] How do I create a cursor on a query in Zope ----- Original Message ----- From: Marten Quadland <mquadland@indigonetworks.com>
I'm trying to build a report that counts multiple things and then places everything in one row. I can't figure out a way to do this with out a cursor.
Any suggestions?
Not unless you can be a little more specific about what you're trying to do <wink>.
From the mention of a cursor, I infer that the multiple things are stored in a SQL database. My immediate reaction is to suggest "SELECT count(*) FROM ...", but then I'm not sure what you mean by "places everything in one row".
Please elaborate.
Ah! Ok, the first approach which pops to mind is this: Suppose SQL_get_names is the first query, and the others are SQL_get_count1, ..., SQL_get_countn. You could then write: <dtml-in SQL_get_names> <dtml-var NAME> <dtml-in SQL_get_count1><dtml-var count></dtml-in><dtml-in SQL_get_count2>...</dtml-in> </dtml-in> Next approach; Suppose queries 1..n differ only in the table name and group field. Define SQL_get_count(table, gfield, NAME) as: select <dtml-var gfield>, count(*) as rcount from <dtml-var table> where <!--#sqltest name=NAME col=name type=string> group by <dtml-var gfield> and write: <dtml-in SQL_get_names> <dtml-var NAME> <dtml-in "('table1', 'field1'), ('table2', 'field2'),..."> <dtml-in "SQL_get_count(table=_['sequence-key'], gfield=_['sequence-item'], NAME=NAME)"><dtml-var rcount></dtml-in> </dtml-in> </dtml-in> Finally, if you want to minimize the number of queries, you could try defining SQL_get_count(table, gfield) as a join query, and use a dictionary and pair of lists to store results. That gets pretty hairy to write in DTML, though. Marten Quadland wrote:
Sorry for the confusion,
I'm trying to open a cursor on a query say:
Open a cursor: select NAME from table
Where I can read one name at a time and perform an action on that name. For example:
select blah, count(*) as table2Count from table2 where table2.name = NAME group by blah
select blah2, count(*) as table3Count from table3 where table3.name = NAME group by blah2
select blah3, count(*) as table4Count from table3 where table3.name = NAME group by blah3
Write a row(NAME, table2count, table3count, table4count)
CLOSE THE CURSOR
The end result would be something like:
NAME Count of Visits Count of Purchases Count of Inquiries Joe 1234 4331 5965 Jane 3766 3534 35 Jeff 3457 6433 3653 Harry 5433 88 2854 Tim 56 2434 9865 . . .
The capitalized NAME is equal to the value of the first response from Query1.
participants (2)
-
Evan Simpson -
Marten Quadland