Hi all, Using max and size in same query is not working in microsoft SQLServer.
To demonstrate the problem, I added a new method in test case org.apache.openjpa.persistence.jpql.functions.TestEJBQLFunction that has the following JPQL function: EntityManager em = currentEntityManager(); String query = "SELECT MAX(SIZE(d.slist)) FROM Department d"; List result = em.createQuery(query).getResultList(); assertNotNull(result); endEm(em); The above fails in MSSQL but runs fine in other DBs. The exception I got is the following: org.apache.openjpa.lib.jdbc.ReportingSQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot perform an aggregate function on an expression containing an aggregate or a s ubquery. {prepstmnt 14513572 SELECT MAX((SELECT COUNT(*) FROM STUD_DEP WHERE STUD_DEP.DEP_ID = t0.id )) FROM Department t0} [code=130, state=HY000] at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.wrap(LoggingConnectionDecorator.java:193) at org.apache.openjpa.lib.jdbc.LoggingConnectionDecorator.access$700(LoggingConnectionDecorator.java:58) ............... at org.apache.openjpa.jdbc.sql.SelectImpl.executeQuery(SelectImpl.java:478) I think this is a limitation of mssql server, because if I run the following query SELECT MAX((SELECT COUNT(*) FROM STUD_DEP WHERE STUD_DEP.DEP_ID = t0.id)) FROM Department t0 then I get the same exception in mssql. when I modify the above to : select Max(studentcount) from (SELECT COUNT(*) studentcount FROM STUD_DEP c0, Department t0 WHERE c0.DEP_ID = t0.id group by c0.DEP_ID) as temp then it works fine. I think the JPQL I used is in compliant with the spec, so openJPA has to convert the JPQL to SQL such that the above exception can be avoided. Please let me know if I have to open a JIRA issue with test case. Thanks, Ravi. -- View this message in context: http://n2.nabble.com/Using-max-and-size-in-same-query-not-working-with-mssql.-tp2480771p2480771.html Sent from the OpenJPA Users mailing list archive at Nabble.com.