openjpa-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From rpalache <ravi.palache...@oracle.com>
Subject Re: Using max and size in same query not working with mssql.
Date Fri, 20 Mar 2009 20:51:16 GMT

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.


Mime
View raw message