Arithmetic expressions in Select and using alias

caron · **Joined:** Fri Mar 11, 2005 10:56 am **Posts:** 2

Hi,

I am using Hibernate 2.1 and database PostgreSQL 7.4.

Does Hibernate support using arithmetic expressions in a HQL query ?

When using the following HQL:

select c.name, (c.cost1+c.cost2+c.cost3)
from Claimant c
order by totalcost

I get the following exception:
net.sf.hibernate.QueryException: aggregate function expected before ( aggregate function expected before ( in SELECT

What I actually need to do is the following:
select c.name, (c.cost1+c.cost2+c.cost3) as totalcost
from Claimant c
order by totalcost

(I need to add the values of three costs and assign it an alias
so that I can later order by this alias)

Can anyone help?

Thanks
Caron

caron · **Joined:** Fri Mar 11, 2005 10:56 am **Posts:** 2

The answer is to use a derived property.

sven · **Posted:** Mon Aug 22, 2005 8:34 am

Hibernate 3 supports arithmetic operators, I don't think Hibernate 2 does in this case.

Though, have you tried the following?

Code:

select c.name, sum(c.cost1+c.cost2+c.cost3)
from Claimant c

wresp.

Code:

select c.name
from Claimant c
order by sum(c.cost1+c.cost2+c.cost3)

I think this will work with Hibernate 2, but an alias for your aggregate function will probably only work with a recent version of Hibernate 3.

Best regards
Sven