Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with HQL using aggregate function in where/having clause

 
Otto Teixeira
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi.
In my schema, an item can be transferred many times between units.

I want to get all the items that, in a specific date, were at a specific unit. I tried:



It says "group function is not allowed here".

So I tried using group by:

This works, but I only get the items' ids. I need the whole object.

How can I do this? Thanks.



Edit: I figured out I could wrap this last query, like this:

But that implies 2 selects. There should be a better way.
 
John Bengler
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Otto,

I'm just curious, why do you have to use the "max" on the date?

Do you want to exclude all item with a maximum date higher than your parameter?



John
 
Otto Teixeira
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I want items which last transfer (up to the parameter) were to a certain unit.
Is there a different way besides using max?
 
John Bengler
Ranch Hand
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hm, I was thinking of EMPTY resp. NOT EMPTY with a suitable relation..

JPA not empty

But I'm afraid in your case this won't help..


Besides: Are you sure that your select works like want..? Doesn't it just use the max date where the unit matches your criterion and hide newer transfers if they belong to another unit?
 
Otto Teixeira
Greenhorn
Posts: 12
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's for a report that will list items' situation in a specific date.
So I only want items transferred (last transfer) to that unit until that date.

But I realized I'll have to use SQL instead. This is just part of the query. I'll need union and minus, and HQL doesn't implement that =/
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic