You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by rpalache <ra...@oracle.com> on 2009/03/15 08:40:27 UTC
Using max and size in same query not working with mssql.
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.
Re: Using max and size in same query not working with mssql.
Posted by rpalache <ra...@oracle.com>.
Hi,
Here is the requested information:
MS SQL Version: SQL Server 2000 SP3
JDBC driver : Microsoft SQL Server 2000 Driver for JDBC
Regards,
Ravi.
Donald Woods wrote:
>
> 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.
>
>
--
View this message in context: http://n2.nabble.com/Using-max-and-size-in-same-query-not-working-with-mssql.-tp2480771p2511100.html
Sent from the OpenJPA Users mailing list archive at Nabble.com.
Re: Using max and size in same query not working with mssql.
Posted by Donald Woods <dw...@apache.org>.
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.