• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Replacing resultset values

 
Greenhorn
Posts: 28
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have an event entity with a composite event id. I have a basic named query like the following which works as expected.



The (:param is null or evt.parm = :param) in the where clause expression "short circuits" which allows me to use one query for multiple cases (with different parameters set to null).

I have facade code like:





Which would return results something like:

//uid, pos, sn, date, rating
1,1,1,"01-03-2010", 1
2,1,1,"01-03-2010", 2
3,1,1,"01-04-2010", 3
4,1,1,"01-05-2010", 4
5,1,1,"01-06-2010", 3

Now I would like a similar query that return a List<Event> with aggregate Sum values instead of the individual rating, to get something like:

//pos, sn, rating
1,1,3
1,2,10

I think I may need to use something like

select evt.sn, evt.pos, sum(evt.rating) as rating from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;

or

select NEW Event( evt.eventId, evt.uid, evt.sn, evt.pos, sum(evt.rating) as rating) from Event evt where evt.sn in ( 1,2) and date >= DATE '01-01-2010' and date <= DATE '02-01-2010' Group by sn, pos;

Some of my confusion on this may be due to an entity design issue, but not sure. Is this good way to do this or is there a better way to do this? Should I have a new class (i.e. for use in a List<ResultClass>) for the results? Or use an Object [] type for the results? Or use an Object[] and then populate an individual Event?

Hope this makes sense..

Eric
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic