java - Spring data jpa FINDALL with SQL Server -

i migrated mysql sql server, , had following error :

the column "statut_ndf_tb.sortid" not valid in order clause because not contained in aggregate function or group clause 

here function :

@override public list<ndf> rechercherndfs(demande<ndf> demande) {     final list<specification<ndf>> specificationlist = specificationshelper.choosespecifications(ndfspecification.class, demande);     final specifications<ndf> specifications = specificationshelper.buildwhereclause(specificationlist);     final pageable page = specificationshelper.buildpage(demande);     final page<ndf> resultat = ndfrepository.findall(specifications.where(specifications), page);     return resultat.getcontent(); } 

i call function following :

demande<ndfdto> demande = new demande<ndfdto>(); demande.addvariable("statutnoequal", "an"); demande.setconverterid(enummapper.mapping_ndf_info.tostring()); ndfservicessilo.rechercherndfs(demande); 

and statutnoequal :

public static specification<ndf> statutnoequal(final string codestatut) {     return new specification<ndf>() {         @override         public predicate topredicate(root<ndf> ndfroot, criteriaquery<?> query, criteriabuilder cb) {             query.orderby(cb.asc(ndfroot.get(ndf_.refetat).get(statutndf_.sortid)));             return cb.notequal(ndfroot.get(ndf_.refetat).get(statutndf_.code), codestatut);         }     }; } 

i use order by clause data, call count same specification (with order by) returns error, here error :

13:06:24.316 [http-nio-8080-exec-6] debug org.hibernate.sql -      select         count(ndf0_.id) col_0_0_              ndf_tb ndf0_ cross      join         statut_ndf_tb statutndf1_              ndf0_.ref_etat=statutndf1_.id          , statutndf1_.code<>?          , ndf0_.ref_ress=2406      order         statutndf1_.sortid asc 13:06:24.316 [http-nio-8080-exec-6] trace o.h.type.descriptor.sql.basicbinder - binding parameter [1] [varchar] - [an] 13:06:24.316 [http-nio-8080-exec-6] warn  o.h.e.jdbc.spi.sqlexceptionhelper - sql error: 8127, sqlstate: s0001 13:06:24.316 [http-nio-8080-exec-6] error o.h.e.jdbc.spi.sqlexceptionhelper - la colonne "statut_ndf_tb.sortid" n'est pas valide dans la clause order parce qu'elle n'est pas contenue dans une fonction d'agrégation ou dans la clause group by. 13:06:24.394 [http-nio-8080-exec-6] error f.p.s.b.e.customexceptionhandler - javax.faces.facesexception: fr.percall.si.base.exception.serviceexecutionexception: not extract resultset; sql [n/a]; nested exception org.hibernate.exception.sqlgrammarexception: not extract resultset     @ fr.percall.si.view.util.basiclazydatamodel.load(basiclazydatamodel.java:71)     @ org.primefaces.component.datatable.datatable.loadlazydata(datatable.java:841)     @ org.primefaces.component.datatable.datatablerenderer.prerender(datatablerenderer.java:95)     @ org.primefaces.component.datatable.datatablerenderer.encodeend(datatablerenderer.java:83)     @ javax.faces.component.uicomponentbase.encodeend(uicomponentbase.java:674)     @ javax.faces.component.uidata.encodeend(uidata.java:1721)     @ javax.faces.component.uicomponentbase.encodeall(uicomponentbase.java:554)     @ javax.faces.component.uicomponentbase.encodeall(uicomponentbase.java:550)     @ javax.faces.component.uicomponentbase.encodeall(uicomponentbase.java:550)     @ javax.faces.component.uicomponentbase.encodeall(uicomponentbase.java:550)     @ org.apache.myfaces.view.facelets.faceletviewdeclarationlanguage.renderview(faceletviewdeclarationlanguage.java:1891)     @ org.apache.myfaces.application.viewhandlerimpl.renderview(viewhandlerimpl.java:313)     @ javax.faces.application.viewhandlerwrapper.renderview(viewhandlerwrapper.java:58)     @ org.apache.myfaces.lifecycle.renderresponseexecutor.execute(renderresponseexecutor.java:116)     @ org.apache.myfaces.lifecycle.lifecycleimpl.render(lifecycleimpl.java:267)     @ javax.faces.webapp.facesservlet.service(facesservlet.java:200)     @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:291)     @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)     @ org.apache.tomcat.websocket.server.wsfilter.dofilter(wsfilter.java:52)     @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)     @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:330)     @ org.springframework.security.web.access.intercept.filtersecurityinterceptor.invoke(filtersecurityinterceptor.java:118)     @ org.springframework.security.web.access.intercept.filtersecurityinterceptor.dofilter(filtersecurityinterceptor.java:84)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.access.exceptiontranslationfilter.dofilter(exceptiontranslationfilter.java:113)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.session.sessionmanagementfilter.dofilter(sessionmanagementfilter.java:103)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.authentication.anonymousauthenticationfilter.dofilter(anonymousauthenticationfilter.java:113)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.servletapi.securitycontextholderawarerequestfilter.dofilter(securitycontextholderawarerequestfilter.java:154)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.savedrequest.requestcacheawarefilter.dofilter(requestcacheawarefilter.java:45)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.authentication.abstractauthenticationprocessingfilter.dofilter(abstractauthenticationprocessingfilter.java:199)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.authentication.logout.logoutfilter.dofilter(logoutfilter.java:110)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.context.request.async.webasyncmanagerintegrationfilter.dofilterinternal(webasyncmanagerintegrationfilter.java:50)     @ org.springframework.web.filter.onceperrequestfilter.dofilter(onceperrequestfilter.java:107)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.session.concurrentsessionfilter.dofilter(concurrentsessionfilter.java:125)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.context.securitycontextpersistencefilter.dofilter(securitycontextpersistencefilter.java:87)     @ org.springframework.security.web.filterchainproxy$virtualfilterchain.dofilter(filterchainproxy.java:342)     @ org.springframework.security.web.filterchainproxy.dofilterinternal(filterchainproxy.java:192)     @ org.springframework.security.web.filterchainproxy.dofilter(filterchainproxy.java:160)     @ org.springframework.web.filter.delegatingfilterproxy.invokedelegate(delegatingfilterproxy.java:344)     @ org.springframework.web.filter.delegatingfilterproxy.dofilter(delegatingfilterproxy.java:261)     @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)     @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)     @ org.apache.logging.log4j.web.log4jservletfilter.dofilter(log4jservletfilter.java:67)     @ org.apache.catalina.core.applicationfilterchain.internaldofilter(applicationfilterchain.java:239)     @ org.apache.catalina.core.applicationfilterchain.dofilter(applicationfilterchain.java:206)     @ org.apache.catalina.core.standardwrappervalve.invoke(standardwrappervalve.java:219)     @ org.apache.catalina.core.standardcontextvalve.invoke(standardcontextvalve.java:106)     @ org.apache.catalina.authenticator.authenticatorbase.invoke(authenticatorbase.java:501)     @ org.apache.catalina.core.standardhostvalve.invoke(standardhostvalve.java:142)     @ org.apache.catalina.valves.errorreportvalve.invoke(errorreportvalve.java:79)     @ org.apache.catalina.valves.abstractaccesslogvalve.invoke(abstractaccesslogvalve.java:610)     @ org.apache.catalina.core.standardenginevalve.invoke(standardenginevalve.java:88)     @ org.apache.catalina.connector.coyoteadapter.service(coyoteadapter.java:516)     @ org.apache.coyote.http11.abstracthttp11processor.process(abstracthttp11processor.java:1086)     @ org.apache.coyote.abstractprotocol$abstractconnectionhandler.process(abstractprotocol.java:659)     @ org.apache.coyote.http11.http11nioprotocol$http11connectionhandler.process(http11nioprotocol.java:223)     @ org.apache.tomcat.util.net.nioendpoint$socketprocessor.dorun(nioendpoint.java:1558)     @ org.apache.tomcat.util.net.nioendpoint$socketprocessor.run(nioendpoint.java:1515)     @ java.util.concurrent.threadpoolexecutor.runworker(threadpoolexecutor.java:1142)     @ java.util.concurrent.threadpoolexecutor$worker.run(threadpoolexecutor.java:617)     @ org.apache.tomcat.util.threads.taskthread$wrappingrunnable.run(taskthread.java:61)     @ java.lang.thread.run(thread.java:745) caused by: fr.percall.si.base.exception.serviceexecutionexception: not extract resultset; sql [n/a]; nested exception org.hibernate.exception.sqlgrammarexception: not extract resultset     @ fr.percall.si.services.ndf.impl.ndfservicessiloimpl.rechercherndfs(ndfservicessiloimpl.java:297)     @ sun.reflect.nativemethodaccessorimpl.invoke0(native method)     @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:497)     @ org.springframework.aop.support.aoputils.invokejoinpointusingreflection(aoputils.java:317)     @ org.springframework.aop.framework.reflectivemethodinvocation.invokejoinpoint(reflectivemethodinvocation.java:190)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:157)     @ org.springframework.transaction.interceptor.transactioninterceptor$1.proceedwithinvocation(transactioninterceptor.java:99)     @ org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:281)     @ org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:96)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:207)     @ com.sun.proxy.$proxy213.rechercherndfs(unknown source)     @ fr.percall.si.view.ndf.spec.abstractgestionndfcontroller$1.getresults(abstractgestionndfcontroller.java:226)     @ fr.percall.si.view.util.basiclazydatamodel.load(basiclazydatamodel.java:65)     ... 71 more caused by: com.microsoft.sqlserver.jdbc.sqlserverexception: la colonne "statut_ndf_tb.sortid" n'est pas valide dans la clause order parce qu'elle n'est pas contenue dans une fonction d'agrégation ou dans la clause group by.     @ com.microsoft.sqlserver.jdbc.sqlserverexception.makefromdatabaseerror(sqlserverexception.java:196)     @ com.microsoft.sqlserver.jdbc.sqlserverstatement.getnextresult(sqlserverstatement.java:1454)     @ com.microsoft.sqlserver.jdbc.sqlserverpreparedstatement.doexecutepreparedstatement(sqlserverpreparedstatement.java:388)     @ com.microsoft.sqlserver.jdbc.sqlserverpreparedstatement$prepstmtexeccmd.doexecute(sqlserverpreparedstatement.java:338)     @ com.microsoft.sqlserver.jdbc.tdscommand.execute(iobuffer.java:4026)     @ com.microsoft.sqlserver.jdbc.sqlserverconnection.executecommand(sqlserverconnection.java:1416)     @ com.microsoft.sqlserver.jdbc.sqlserverstatement.executecommand(sqlserverstatement.java:185)     @ com.microsoft.sqlserver.jdbc.sqlserverstatement.executestatement(sqlserverstatement.java:160)     @ com.microsoft.sqlserver.jdbc.sqlserverpreparedstatement.executequery(sqlserverpreparedstatement.java:281)     @ org.apache.tomcat.dbcp.dbcp2.delegatingpreparedstatement.executequery(delegatingpreparedstatement.java:82)     @ org.apache.tomcat.dbcp.dbcp2.delegatingpreparedstatement.executequery(delegatingpreparedstatement.java:82)     @ org.hibernate.engine.jdbc.internal.resultsetreturnimpl.extract(resultsetreturnimpl.java:82)     @ org.hibernate.loader.loader.getresultset(loader.java:2066)     @ org.hibernate.loader.loader.executequerystatement(loader.java:1863)     @ org.hibernate.loader.loader.executequerystatement(loader.java:1839)     @ org.hibernate.loader.loader.doquery(loader.java:910)     @ org.hibernate.loader.loader.doqueryandinitializenonlazycollections(loader.java:355)     @ org.hibernate.loader.loader.dolist(loader.java:2554)     @ org.hibernate.loader.loader.dolist(loader.java:2540)     @ org.hibernate.loader.loader.listignorequerycache(loader.java:2370)     @ org.hibernate.loader.loader.list(loader.java:2365)     @ org.hibernate.loader.hql.queryloader.list(queryloader.java:497)     @ org.hibernate.hql.internal.ast.querytranslatorimpl.list(querytranslatorimpl.java:387)     @ org.hibernate.engine.query.spi.hqlqueryplan.performlist(hqlqueryplan.java:236)     @ org.hibernate.internal.sessionimpl.list(sessionimpl.java:1264)     @ org.hibernate.internal.queryimpl.list(queryimpl.java:103)     @ org.hibernate.jpa.internal.queryimpl.list(queryimpl.java:573)     @ org.hibernate.jpa.internal.queryimpl.getresultlist(queryimpl.java:449)     @ org.hibernate.jpa.criteria.compile.criteriaquerytypequeryadapter.getresultlist(criteriaquerytypequeryadapter.java:67)     @ org.springframework.data.jpa.repository.support.simplejparepository.executecountquery(simplejparepository.java:615)     @ org.springframework.data.jpa.repository.support.simplejparepository.readpage(simplejparepository.java:495)     @ org.springframework.data.jpa.repository.support.simplejparepository.findall(simplejparepository.java:394)     @ sun.reflect.generatedmethodaccessor451.invoke(unknown source)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:497)     @ org.springframework.data.repository.core.support.repositoryfactorysupport$queryexecutormethodinterceptor.executemethodon(repositoryfactorysupport.java:414)     @ org.springframework.data.repository.core.support.repositoryfactorysupport$queryexecutormethodinterceptor.doinvoke(repositoryfactorysupport.java:399)     @ org.springframework.data.repository.core.support.repositoryfactorysupport$queryexecutormethodinterceptor.invoke(repositoryfactorysupport.java:371)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.data.projection.defaultmethodinvokingmethodinterceptor.invoke(defaultmethodinvokingmethodinterceptor.java:61)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.transaction.interceptor.transactioninterceptor$1.proceedwithinvocation(transactioninterceptor.java:99)     @ org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:281)     @ org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:96)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.dao.support.persistenceexceptiontranslationinterceptor.invoke(persistenceexceptiontranslationinterceptor.java:136)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.data.jpa.repository.support.crudmethodmetadatapostprocessor$crudmethodmetadatapopulatingmethodintercceptor.invoke(crudmethodmetadatapostprocessor.java:122)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.aop.interceptor.exposeinvocationinterceptor.invoke(exposeinvocationinterceptor.java:92)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:207)     @ com.sun.proxy.$proxy207.findall(unknown source)     @ fr.percall.si.services.ndf.impl.ndfserviceimpl.rechercherndfs(ndfserviceimpl.java:56)     @ sun.reflect.nativemethodaccessorimpl.invoke0(native method)     @ sun.reflect.nativemethodaccessorimpl.invoke(nativemethodaccessorimpl.java:62)     @ sun.reflect.delegatingmethodaccessorimpl.invoke(delegatingmethodaccessorimpl.java:43)     @ java.lang.reflect.method.invoke(method.java:497)     @ org.springframework.aop.support.aoputils.invokejoinpointusingreflection(aoputils.java:317)     @ org.springframework.aop.framework.reflectivemethodinvocation.invokejoinpoint(reflectivemethodinvocation.java:190)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:157)     @ org.springframework.transaction.interceptor.transactioninterceptor$1.proceedwithinvocation(transactioninterceptor.java:99)     @ org.springframework.transaction.interceptor.transactionaspectsupport.invokewithintransaction(transactionaspectsupport.java:281)     @ org.springframework.transaction.interceptor.transactioninterceptor.invoke(transactioninterceptor.java:96)     @ org.springframework.aop.framework.reflectivemethodinvocation.proceed(reflectivemethodinvocation.java:179)     @ org.springframework.aop.framework.jdkdynamicaopproxy.invoke(jdkdynamicaopproxy.java:207)     @ com.sun.proxy.$proxy208.rechercherndfs(unknown source)     @ fr.percall.si.services.ndf.impl.ndfservicessiloimpl.rechercherndfs(ndfservicessiloimpl.java:275)     ... 86 more 

your select statement not valid sql server statement. if want count of rows in 2 joined tables remove order clause. if want count of number of rows per statutndf1_.sortid need insert group clause before order clause. if want include statutndf1_.sortid in output need include in select list.

see these examples:

set nocount on declare @table table(id int,no int) insert @table values (111,    6), (222,    7), (333 ,   9), (111  ,  8), (333   , 4), (222   , 3),(111   , 7), (222   , 5), (333   , 2)  select  count(*) obs    @table /* result obs ----------- 9 */  select  count(*) obs    @table group    id order   id /* result obs ----------- 3 3 3 */  select  id,count(*) obs    @table group    id order   id /* result id          obs ----------- ----------- 111         3 222         3 333         3 */