Urgent: Example for Report Query

2003-07-21 Thread Aaron Longwell 
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]

Re: Urgent: Example for Report Query

2003-07-21 Thread Ron Gallagher 
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]

Re: Urgent: Example for Report Query

2003-07-21 Thread Aaron Longwell 
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]

Re: Urgent: Example for Report Query

2003-07-21 Thread Aaron Longwell 
Ron,

Using your logic, I'm trying another (less efficient I know) method of 
accomplishing my task. Here's my code:

   Criteria criteria = new Criteria();
   criteria.addOrderBy(performances.payout, true);
   Query query = QueryFactory.newQuery(Band.class, criteria, true);
This code returns Band objects (on which I have an inefficient method to 
find average data for the Performance Collections). My code is working 
correctly, except OJB is giving me duplicate records. I currently have 5 
records in that table, and the first two alphabetically are getting 
repeated when I try to order by performances.Payout. This is unusual.

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]

RE: Urgent: Example for Report Query

2003-07-21 Thread Gelhar, Wallace Joseph 
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]