Issue
In my app I need to use @NamedQuery to find the type of the most frequent operation assigned to specific account
@Entity
@Table(name="\"ACCOUNTOPERATION\"")
@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
"SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
"GROUP BY ao.type HAVING COUNT(ao) = (" +
"SELECT MAX(typeCountQuery.typeCount) " +
"FROM (" +
"SELECT COUNT(aop) as typeCount " +
"FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type" +
") as typeCountQuery" +
")"
)
public class AccountOperation {
@ManyToOne
private Account account;
private BigDecimal amount;
private OperationType type;
...
Right after FROM clause at '(' character, which begins typeCountQuery's body I'm getting
')', ',', GROUP, HAVING, IN, WHERE or identifier expected, got '('
I've read that JPA does not support sub-selects in the FROM clause, so is there any way to rewrite SQL code to still use it in @NamedQuery?
I'm using IntelliJ IDE with H2 DB and with eclipselink and javax.persistence in dependencies.
Solution
In JPQL, you cannot use subqueries. To resolve this issue, you need to use some keywords like ALL, ANY, which work similiar.
So in your situation it could be:
@NamedQuery(name="AccountOperation.findTypeOfMostFrequentOperation", query="" +
"SELECT ao.type from AccountOperation ao WHERE ao.account.id = ?1 " +
"GROUP BY ao.type HAVING COUNT(ao) >= ALL (" +
"SELECT COUNT(aop) as typeCount " +
"FROM AccountOperation aop WHERE aop.account.id = ?1 GROUP BY aop.type)"
Answered By - KosztDojscia
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)