Which version of MS SQL and which JDBC driver are you using?
-Donald
rpalache wrote:
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.
