HQL with case, sum and max

Question

I’m with a query that is giving problem, I believe the problem occurs for not allow to use the function max, within the function sum, am I correct?

How can I get around this? Because I need to add up all the values except the last one row.

Follow the query:

"SELECT new project.domain.vo.MediaPagVo(" +
                    "a.nomeUsuario, " +
                    "a.cpfUsuario, " +
                    "a.cnpjUnidadeUsuario,        " +
                    "a.nomeUnidadeUsuario,        " +
                    "a.codigoGrupoUsuario,        " +
                    "a.grupo.id AS idGrupo,         " +
                    "CASE " +
                        "WHEN MAX(a.pre) - MIN(a.pre) > 0 " +
                        "THEN ((MAX(a.pre) - MIN(a.pre)) / SUM(CASE WHEN max(a.dtRequisicao) > a.dtRequisicao THEN a.valorTotal ELSE 0 END)) " +
                        "ELSE ((MAX(a.pos) - MIN(a.pos)) / SUM(CASE WHEN max(a.dtRequisicao) >= a.dtRequisicao THEN a.valorTotal ELSE 0 END)) " +
                    "END AS mediaConsumo, " +
                    "CASE " +
                        "WHEN MAX(a.pre) - MIN(a.pre) > 0 " +
                        "THEN " + Tipo.PRE.getValue() + " " +
                        "ELSE " + Tipo.POS.getValue() + " " +
                    "END AS tipoPag) " +
            "FROM " +
                    " Pagamento a " +
            "WHERE " +
                    "a.status = " + StatusPagamento.Concluido.getValue() + " AND " +
                    "a.dataRequisicao BETWEEN :inicio and :fim AND " +
                    "( :idGrupo IS NULL OR a.grupo.id = :idGrupo) " +
                   "GROUP BY " +
                    " a.nomeUsuario, a.cpfUsuario, a.cnpjUnidadeUsuario, a.nomeUnidadeUsuario, a.codigoGrupoUsuario, a.grupo.id, " +
            "HAVING (MAX(a.pos) - MIN(a.pos) > 0 OR MAX(a.pre) - MIN(a.pre) > 0) AND " +
                    "( :tipoConsumo IS NULL OR " +
                        ":tipoConsumo = " + Tipo.PRE.getValue() + " AND " +
                        "(MAX(a.pre) - MIN(a.pre) > 0) " +
                        "OR :tipoConsumo = " + Tipo.POS.getValue() + " AND " +
                        "(MAX(a.pos) - MIN(a.pos) > 0) ) ";

The error started to occur after I modified this excerpt by adding the case within the sum:

"CASE WHEN MAX(a.pre) - MIN(a.pre) > 0 " +
                        "THEN ((MAX(a.pre) - MIN(a.pre)) / SUM(CASE WHEN max(a.dtRequisicao) > a.dtRequisicao THEN a.valorTotal ELSE 0 END)) " +
                        "ELSE ((MAX(a.pos) - MIN(a.pos)) / SUM(CASE WHEN max(a.dtRequisicao) >= a.dtRequisicao THEN a.valorTotal ELSE 0 END)) " +
                    "END AS mediaPag, " 

The error returned by java:

Could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Caused by: java.sql.SQLSyntaxErrorException: ORA-00937: não é uma função de grupo de grupo simples

My goal was to add up all the payments of that period, except the last one. Unfortunately, I could not only touch the filter, because the max(a.pre), max(a.pos) may be from the last payment.

Does anyone have any idea?

Answer 1

This error refers to Oracle itself:

ORA-00937: not a simple group function

In English:

ORA-00937: not a single-group group Function tips

From what I understand, you added the fields a.dtRequisicao and a.valorTotal within that SUM. The problem is that they are not stated in the GROUP BY and (for what understanding of the error) which also needs to be stated in your SELECT

So the correction would be to add them to the GROUP BY:

"GROUP BY " + " a.nomeUsuario, a.cpfUsuario, a.cnpjUnidadeUsuario, a.nomeUnidadeUsuario, a.codigoGrupoUsuario, a.grupo.id, a.dtRequisicao, a.valorTotal" +

AND IN SELECT:

"SELECT new project.domain.vo.MediaPagVo(" +
                "a.nomeUsuario, " +
                "a.cpfUsuario, " +
                "a.cnpjUnidadeUsuario,        " +
                "a.nomeUnidadeUsuario,        " +
                "a.codigoGrupoUsuario,        " +
                "a.grupo.id AS idGrupo,         " +
                "a.dtRequisicao, a.valorTotal,         " +

A hint: try not to use Train Wreck in JPQL/HQL, even more so by repeating it in several places in the same query. Example: a.grupo.id. Often the JPA framework creates weird queries because of this and, in some more complex cases, errors occur to generate the SQL query. The solution is to make JOIN explicit:

JOIN a.grupo grupo

And use grupo.id.