CODEWITHSUNDEEP
javasqlhibernatehql
Shinoy Shaji
Shinoy Shaji

Reputation: 397

Can we use to_char( ) with GROUP BY in HQL

while executing the following query using Hibernate

select to_char(vdadCloseDate,'yyyymm'), count(*) from RmDashboardAccountDataBe where 1=1  and vdadRmId in('MK13','MK11') GROUP BY TO_CHAR(vdadCloseDate,'YYYYMM')

I'm getting the following exception,

java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

Is there any way to handle this issue?

Upvotes: 0

Views: 3504

Answers (1)

Littlefoot
Littlefoot

Reputation: 143033

This is "pure" Oracle SQL (i.e. not HQL) which looks exactly like your query (I had to use different table and column names, though):

SQL> select to_char(hire_date, 'yyyymm'), count(*)
  2  from employees
  3  where department_id in (10, 20)
  4  group by to_char(hire_date, 'yyyymm');

TO_CHA   COUNT(*)
------ ----------
200309          1
200508          1
200402          1

SQL>

So - yes, it works OK.

This is a link to HQL Group by clause which also suggests that such a query is perfectly valid (have a look so that I wouldn't have to copy/paste its contents over here).

That's why I asked whether you're sure that this is the query that returned ORA-00979 error. As you responded that it is, huh, I wouldn't know what to say ...

Upvotes: 1

Related Questions