• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Paul Clapham
  • Bear Bibeault
  • Jeanne Boyarsky
Sheriffs:
  • Ron McLeod
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Jj Roberts
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • salvin francis
  • Scott Selikoff
  • fred rosenberger

Help with HQL using aggregate function in where/having clause

 
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.
 
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 =/
 
Legend has it that if you rub the right tiny ad, a genie comes out.
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic