SUM and MAX function in Hibernate

3

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
    
asked by anonymous 15.07.2015 / 02:46

1 answer

1

SQL exception demonstrates error ORA-00937 :

  

SQL Error: 937, SQLState: 42000
ORA-00937: not a single-group   group function   

I believe that ORACLE did not identify grouping by field:

sum(max(VLRETQCPR)) as valorEstoqueComprador

Because of the lack of alias (generated by hibernate - this _ ), cause ambiguity:

group by this_.CODCPR, this_.NOMCPR, this_.VLRETQCPR

To refer to Criteria root class Mercadoria.class you must use the {alias} . Being:

projs.add(Projections.sqlProjection("sum(max({alias}.VLRCOMPR)) as valorComp", new String[] {"valorComp"} , new Type[] {BigDecimalType.INSTANCE}));                


    
16.07.2015 / 16:26