Hibernate SUM and MAX function

Asked

Viewed 690 times

3

Hello!

I have the following scenario, I have a particular Ibernate where I get the highest value of a column when I pass an affiliate code as a parameter. Example: There are only two branches if the code is 1 is a value, if it is 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 subsidiaries. I’m not getting that result by ultimatizing Criteria, if anyone can help ?

The excerpt 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
java java-ee hibernate jpa

  • what exception or dump the generated query?

  • Thank you for the reply Reginaldo Soares. I edited my question adding the generated SQL and the obtained error.

  • I’m reviewing my answer, it’s incorrect.

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 has not identified the cluster 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 marking {alias}. Being:

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


  • Thank you for the reply Reginaldo Soares and sorry for the delay in responding. I will do the tests, and put the results!

  • @Edegersil I did some tests today when I arrived at the office, and it did not work will be necessary a sub-select, I am doing some tests here and put the correct version, sorry.

  • OK Reginaldo Soares, thanks. I also tried and it didn’t work.

Browser other questions tagged java java-ee hibernate jpa

You are not signed in. Login or sign up in order to post.