0

I know that this problem is already very well covered, but nonetheless, I can not solve it unfortunately.

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Column 'personal.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The problem disappears if you remove the first one "group by p"

@Query(value = "select p from Inspection i left join i.cohort c left join i.personal p left join fetch p.personal ip left join fetch ip.transfer t left join fetch t.profession left join fetch t.department left join fetch p.outsidePersonal op left join fetch op.organisation where (:personalType is null or p.type = :personalType) and (:department is null or t.department=:department) and (:organisation is null or op.organisation=:organisation) and (:unit is null or i.unit = :unit) and (:dateFrom is null or i.date>=:dateFrom) and (:dateTo is null or i.date<=:dateTo) and (:controlType is null or c.parent=:controlType) and (:measureType is null or i.cohort=:measureType) group by p", 
countQuery = "select count(p) from Inspection i left join i.cohort c left join i.personal p left join p.personal ip left join ip.transfer t left join p.outsidePersonal op where (:personalType is null or p.type = :personalType) and (:department is null or t.department=:department) and (:organisation is null or op.organisation=:organisation) and (:unit is null or i.unit = :unit) and (:dateFrom is null or i.date>=:dateFrom) and (:dateTo is null or i.date<=:dateTo) and (:controlType is null or c.parent=:controlType) and (:measureType is null or i.cohort=:measureType) group by p")
Tanner
  • 22,205
  • 9
  • 65
  • 83
Andrii Shulhin
  • 11
  • 1
  • 4
  • 4
    The error message says it pretty clear! You typically GROUP BY the same columns as you SELECT, except those who are arguments to set functions. – jarlh Mar 09 '18 at 09:57
  • Possible duplicate of [Column "invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"](https://stackoverflow.com/questions/18258704/column-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggr) – Tanner Mar 09 '18 at 10:23
  • When using the database in memory everything works without problems. How can this be explained? How correctly to correct such error? – Andrii Shulhin Mar 09 '18 at 10:57

0 Answers0