HQL with case, sum e max

Question

I have a query that is giving problem, I believe the problem occurs because it does not allow to use the max function, inside the max function, am I correct?

How can I get around this? I need to add all values ​​except for the last row.

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 began to occur after I modified this section, adding the case inside 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

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

My goal was to add up all the payments for that period, minus the last. Unfortunately, I could not just move the filter, since max (a.pre), max (a.pos) may be the last payment. Does anyone have any ideas?

Answer 1

Maybe it is also worth asking how to refactor this query. Is performance a problem? I mean instead of running one query you would run several smaller ones would the system be very slow.

For ex. get MIN MAX(a.pre) in a separate query, do the SUM(CASE WHEN in Java code and pass these values as parameters.

If you decide to refactor, it would be safe to first add some tests to see the behaviour of the old query with certain values. Then do the refactoring. And finally, re-run the tests you added to check that you didn't add any bug.