Hi
Hereunder two tests to illustrate it, the first one will not work, whereas the second will.
It should be written in the documentation.
Regards
Michael
@Test
public void selectCaseWhenGroupCUBA() {
String query = "select " +
"(case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) as date" +
", SUM(e.grossAmount) " +
"from busy$CustomerInvoiceSimpleLine e " +
"group by (case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) " +
"order by date";
persistence.createTransaction().execute((em) -> {
dump(em.createQuery(query).getResultList());
});
}
@Test
public void selectCaseWhenGroupJPA() {
String query = "select " +
"(case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) as date" +
", SUM(e.grossAmount) " +
"from busy$CustomerInvoiceSimpleLine e " +
"group by (case when e.invoice.creditNoteFor is not null then e.invoice.creditNoteFor.date else e.invoice.date end) " +
"order by date";
persistence.createTransaction().execute((em) -> {
dump(em.getDelegate().createQuery(query).getResultList());
});
}