Filtering on calculated columns in hibernate? (HAVING)

Question

Say I have a table with two columns, X and Y. I would like to run the following query via hibernate:

SELECT X*X + Y*Y AS Distance, POINTS.* from POINTS 
WHERE X > 0 
AND Y > 0
HAVING Distance < 50

How do I achieve it via hibernate? Can you provide a code sample?

Edit - This question seems heavily related. The only reason I'm not closing this one as a duplicate, is because it provides a much simpler use case (no GROUP BY).

That syntax is invalid. HAVING without grouping using GROUP BY is not permitted by SQL — , Nov 27 '10 at 13:27
@a_horse - It is not in mysql. Added tag. Besides, you can massage my query into a globally valid query if you want. — ripper234, Nov 27 '10 at 13:46
The standard way would be to use a derived table in the SELECT and apply the WHERE condition for the column alias in the outer SELECT. — , Nov 27 '10 at 13:56

score 0 · Answer 1 · answered Nov 27 '10 at 16:46

In most dialects of SQL, you cannot use the 'display label' or the name in 'AS name' to refer to an expression inside the body of the query - much to the chagrin of people. However, you can use the expression in the WHERE clause.

SELECT X*X + Y*Y AS DistanceSquared, POINTS.* from POINTS 
 WHERE X > 0 
   AND Y > 0
   AND (X * X + Y * Y) < 50;

The HAVING clause is associated with aggregates and comparisons on aggregates:

SELECT name, COUNT(*)
  FROM SomeWhere
 GROUP BY Name
HAVING COUNT(*) > 1;

The conditions in a HAVING clause must (should) involve at least one 'direct' aggregate; the other term in the condition might be a constant (as shown) or another direct aggregate, or a sub-query, possibly involving 'indirect' aggregates. (AFAIK, I've invented the 'direct/indirect' terminology for this answer - you probably won't be able to search for the expression usefully.)

Filtering on calculated columns in hibernate? (HAVING)

1 Answers1