Try this...
String fields[] = new String[]{
/* 0 */ "Name",
/* 1 */ "avg(Performances.Payout)",
/* 2 */ "avg(Performances.Attendance)",
/* 3 */ "ContactPerson",
/* 4 */ "ContactPhone"};
criteria = new Criteria();
criteria.addGroupBy(fields[0]);
criteria.addGroupBy(fields[3]);
criteria.addGroupBy(fields[4]);
criteria.addOrderByAscending("fields[2]");
ReportQueryByCriteria report = new ReportQueryByCriteria(Band.class,
fields, criteria);
//OJB is using the mapping from Band to translate Band fields into
database columns
itr = broker.getReportQueryIteratorByQuery(report);
while (itr.hasNext()) {
Object[] results = (Object[])itr.next(); //returns Object[]
//Object[] matching columns specified in fields[] definition
}
-----Original Message-----
From: Aaron Longwell [mailto:[EMAIL PROTECTED]
Sent: Monday, July 21, 2003 11:21 AM
To: OJB Users List
Subject: Re: Urgent: Example for Report Query
Ron,
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]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
