Question:
Hi!
I have the following scenario, I have a criterion hibernate where I get the largest value of a column when I pass a branch code as parameter. Example: There are only two branches if the code 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 like to add the two values of the two branches. I'm not getting this result using Criteria, if anyone can help?
The commented snippet 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
MISTAKE
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
Answer:
The SQL exception demonstrates the ORA-00937 error:
SQL Error: 937, SQLState: 42000
ORA-00937: not a single-group group function
I believe that ORACLE did not identify the grouping by field:
sum(max(VLRETQCPR)) as valorEstoqueComprador
because the lack of the alias (generated by hibernate – this_ ), causes ambiguity:
group by this_.CODCPR, this_.NOMCPR, this_.VLRETQCPR
To reference the Criteria root class Mercadoria.class you must use the {alias} tag. Being:
projs.add(Projections.sqlProjection("sum(max({alias}.VLRCOMPR)) as valorComp", new String[] {"valorComp"} , new Type[] {BigDecimalType.INSTANCE}));