In my application I have an accounting table with columns amount, paidAmount, fine. Here I need to find accounts with balance zero by adding these 3 column and matching it with zero.
Below is my hql query which results an error.
query = session.createQuery("FROM Accounts where accountId = :accId and SUM(amount + paidAmount + fine) = 0 order by name");
with error as:
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not extract ResultSet
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1613)
.......
... 106 more
Caused by: java.sql.SQLException: Invalid use of group function
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1003)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
... 121 more
How can I execute this where condition using HQL query
You have a simple syntax error.
Your query should be:
query = session.createQuery("FROM Accounts where accountId = :accId and (amount + paidAmount + fine) = 0 order by name");
SUM() cannot be used like that.
Or maybe you are trying to do something equivalent to:
SELECT * FROM `table` GROUP BY <some_column> HAVING SUM(column1+column2)>0 ORDER BY column_name ASC
If that is the case you can use GROUP BY and HAVING.
2