Hello
I have the following scenario, I have a hibernate criter where I get the highest value from a column when I pass a branch code as a parameter. Example: There are only two branches if the code is 1 is a value, if 2 is another value that is returned.
What I want and am not getting, is when no branch parameter is passed, I would add the two values of the two branches. I'm not getting this result using Criteria, if anyone can help?
The snippet commented on in the code is what I'm trying to do!
My Code
Criteria cri = getSession().createCriteria(Mercadoria.class);
ProjectionList projs = Projections.projectionList();
if ("COMPRADOR".equals(agrupamento)) {
projs.add(Projections.groupProperty("codigoComp"), "codigoComp");
projs.add(Projections.groupProperty("nomeComp"), "nomeComp");
} else if ("FORNECEDOR".equals(agrupamento)) {
projs.add(Projections.groupProperty("codigoFornecedor"), "codigoFornecedor");
projs.add(Projections.groupProperty("nomeFornecedor"), "nomeFornecedor");
}
projs.add(Projections.countDistinct("codigoProduto"), "quantidadeGiroInsuficiente");
projs.add(Projections.sum("valorEst"), "valorEst");
if (codigoFilial == null) {
/*Esse if é onde estou tentando calcular o valor máximo das duas filiais quando não foi informado o codigo de filial*/
projs.add(Projections.groupProperty("valorComp"), "valorComp");
projs.add(Projections.sqlProjection("sum(max(VLRCOMPR)) as valorComp", new String[] {"valorComp"} , new Type[] {BigDecimalType.INSTANCE}));
}else {
projs.add(Projections.max("valorComp"), "valorComp");
}
projs.add(Projections.max("valorFornecedor"), "valorFornecedor");
projs.add(Projections.max("valorCh"), "valorCh");
projs.add(Projections.sum("quantidade"), "quantidade");
projs.add(Projections.sum("quantidadeMedia"), "quantidadeMedia");
projs.add(Projections.sum("valorMedia"), "valorMedia");
projs.add(Projections.max("data"), "data");
projs.add(Projections.max("quantidadeUltima"), "quantidadeUltima");
cri.setProjection(projs);
if (codigoFilial != null) {
cri.add(Restrictions.eq("codigoFilial", codigoFilial));
}
if (codigoComprador != null) {
cri.add(Restrictions.eq("codigoComp", codigoComprador));
}
if (codigoFornecedor != null) {
cri.add(Restrictions.eq("codigoFornecedor", codigoFornecedor));
}
return cri.setResultTransformer(Transformers.aliasToBean(Mercadoria.class)).list();
SQL GENERATED
select this_.CODCPR as y0_,
this_.NOMCPR as y1_,
count(distinct this_.CODPRO) as y2_,
sum(this_.VLRETQMGI) as y3_,
this_.VLRETQCPR as y4_,
sum(max(VLRETQCPR)) as valorEstoqueComprador,
max(this_.VLRETQFRN) as y6_,
max(this_.VLRETQCTG) as y7_,
sum(this_.QDEETQMGI) as y8_,
sum(this_.MEDVND) as y9_,
sum(this_.VLRMEDVND) as y10_,
max(this_.DATULTETD) as y11_,
max(this_.QDEULTETD) as y12_
from MED.VMERDETALHADO this_
group by this_.CODCPR, this_.NOMCPR, this_.VLRETQCPR
ERROR
08:48:55,613 WARN [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost/127.0.0.1:9090-5) SQL Error: 937, SQLState: 42000
08:48:55,613 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (http-localhost/127.0.0.1:9090-5) ORA-00937: not a single-group group function
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:122) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.getResultSet(Loader.java:2031) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1832) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.doQuery(Loader.java:899) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.doList(Loader.java:2516) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.doList(Loader.java:2502) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2332) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.Loader.list(Loader.java:2327) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:124) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1661) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:374) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.6.0_33]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) [rt.jar:1.6.0_33]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_33]
at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_33]
at org.jboss.as.ee.component.ManagedReferenceMethodInterceptorFactory$ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptorFactory.java:72) [jboss-as-ee-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:58) [jboss-as-ee-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.invocation.WeavedInterceptor.processInvocation(WeavedInterceptor.java:53) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ee.component.interceptors.UserInterceptorFactory$1.processInvocation(UserInterceptorFactory.java:58) [jboss-as-ee-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43) [jboss-as-ejb3-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.jpa.interceptor.SBInvocationInterceptor.processInvocation(SBInvocationInterceptor.java:47) [jboss-as-jpa-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.invocation.InitialInterceptor.processInvocation(InitialInterceptor.java:21) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.invocation.ChainedInterceptor.processInvocation(ChainedInterceptor.java:61) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ee.component.interceptors.ComponentDispatcherInterceptor.processInvocation(ComponentDispatcherInterceptor.java:53) [jboss-as-ee-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ejb3.component.pool.PooledInstanceInterceptor.processInvocation(PooledInstanceInterceptor.java:51) [jboss-as-ejb3-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final-redhat-2.jar:1.1.1.Final-redhat-2]
at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:226) [jboss-as-ejb3-7.2.0.Final-redhat-8.jar:7.2.0.Final-redhat-8]
... 107 more
Caused by: java.sql.SQLException: ORA-00937: not a single-group group function
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:219) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:813) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:854) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3370) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3415) [ojdbc14-10.2.0.4.0.jar:Oracle JDBC Driver version - "10.2.0.4.0"]
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56) [hibernate-core-4.2.0.Final-redhat-1.jar:4.2.0.Final-redhat-1]
... 144 more