CODEWITHSUNDEEP
javasql-serverhibernate
gunnar247
gunnar247

Reputation: 304

SQL generated by Hibernate invalid for MS SQL

Hibernate (HQL) generated the following SQL for which I inserted the parameters:

select 
sum(activity0_.calculated_work) as col_0_0_
, employee2_.id as col_1_0_
, projectele1_.id as col_2_0_ 

from 
activity activity0_ 
inner join generic_object activity0_1_ on activity0_.id=activity0_1_.id 
left outer join project_element projectele1_ on     activity0_.project_element_id=projectele1_.id 
left outer join employee employee2_ on activity0_.owner_id=employee2_.id 
left outer join org_unit orgunit3_ on employee2_.org_unit_id=orgunit3_.id 

where 
activity0_1_.deleted=0 and 
activity0_.client_id=22 

 group by 
 employee2_.id order by SUM(activity0_.calculated_work) DESC

Error message: Column 'project_element.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I executed this SQL directly in the SQL Server Studio with the same result. I commented this line:

, projectele1_.id as col_2_0_

The SQL was then accepted by the SQL Server

The table project_element definitely has a column with the name id it is also referenced in the LEFT OUTER JOIN and there this column is not causing an error.

Removing the alias projectele1_ had no effect.

To me this looks like a really simple SQL statement. I cannot imagine what is wrong with it and the error message is not helping at all. Any ideas what could be wrong with the SQL?

Upvotes: 0

Views: 275

Answers (2)

gunnar247
gunnar247

Reputation: 304

My mistake. I should have read the error message several times. projectele1_id is not in the group by clause. MS SQL does not allow to include such a column into the select list. This seems to be a consistency check. Too bad though that the usage of HQL leads to such an exception in SQL Server but not in MySQL Server.

Upvotes: 0

Afsun Khammadli
Afsun Khammadli

Reputation: 2068

Your SQL syntax is wrong.If you add projectele1_.id to group by clause it will work.Only aggregate functions work in select statement with group by clause.Or if you remove projectele1_.id from select it will work fine.

Upvotes: 3

Related Questions