Thanks for the prompt response... your code looks great... except for a few items. criteria.addGroupBy() and addOrderBy*() have been deprecated... I think the same methods on the query object are preferred now. That's sort of irrelevant though.
I am getting the error: "Error reading from result set: Column 'Genre' not found." Genre is another property on the Band object and a column in my SQL database. It is irrelevant to this report, but OJB seems to be hung up trying to populate a Band object with a Genre field that doesn't exist in the query.
1) Why is OJB creating a Band object.... this is a report query... isn't it supposed to return raw data and ignore my Business objects?
2) When this report query finally works, how do I access that data? Is it a collection of hashmaps keyed by the column name?
Once again, thank you for your prompt and helpfuil response.
Thanks, Aaron
Ron Gallagher wrote:
Aaron --
This should create the query you need.
String fields[] = new String[]{ /* 0 */ "Name", /* 1 */ "avg(Performances.Payout)", /* 2 */ "avg(Performances.Attendance)", /* 3 */ "ContactPerson", /* 4 */ "ContactPhone"};
// Assemble the criteria. Criteria criteria = new Criteria(); criteria.addGroupBy(fields[0]); criteria.addGroupBy(fields[3]); criteria.addGroupBy(fields[4]); criteria.addOrderByAscending("avg(Performances.Attendance)");
// Assemble the query. ReportQueryByCriteria query = new ReportQueryByCriteria( Band.class, fields, criteria);
Then, just use the getCollectionByQuery method on the PB to get the results.
HTH
Ron Gallagher Atlanta, GA [EMAIL PROTECTED]
From: Aaron Longwell <[EMAIL PROTECTED]> Date: 2003/07/21 Mon AM 11:53:46 EDT To: OJB User <[EMAIL PROTECTED]> Subject: Urgent: Example for Report Query
I cannot figure out how to do a report query. Here's my set up:
Class: Band Fields: Name, ContactPerson, ContactPhone Collection: Performances
Class: Performance Fields: Payout, Attendance
I need to do a report query that will give me 5 columns: Band.Name, Band.ContactPerson, Band.ContactPhone, Avg(Performance.Payout), Avg(Performance.Attendance).
I then need to order by Name, Attendance or Payout based on the user's selection.
In real SQL it would look something like this:
SELECT Band.Name, AVG(Performance.Payout) As Payout, AVG(Performance.Attendance) As Attendace, Band.ContactPerson, Band.ContactPhone
FROM Band
INNER JOIN Performance ON Performance.BandID = Band.ID
GROUP BY Band.Name, Band.ContactPerson, Band.ContactPhone ORDER BY Attendance ASC
I have been thoroughly impressed with the quality of the OJB product, but I have had the hardest time learning how to use it using only the online documentation. I am willing to help out with documentation writing of examples to answer questions like mine above.
Thank you for your time, Aaron Longwell
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
