HQL and GROUP BY

nanndoj · **Joined:** Tue Aug 14, 2007 11:47 am **Posts:** 6

Hi all,

When I try to execute the HQL below...

Code:

SELECT
  m.date as date,
  m.documentNumber as documentNumber,
  m.amount as amount,
  m.description as description,
  m.accountTaxValue as accountTaxValue,
  m.account as account,
  (SELECT sum(amount) from Movement where date > '20-02-2011') as primo
FROM
  Movement m
GROUP BY
  m.date,
  m.documentNumber,
  m.amount,
  m.description,
  m.accountTaxValue,
  m.account

... I got the error Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

In the generated SQL below I could see the hibernate parser adds the account fields to my SELECT expression but not to the GROUP BY expression

Code:

  select movement0_.MOV_DATE as col_0_0_, 
       movement0_.MOV_DOCUMENT_NUMBER as col_1_0_, 
       movement0_.MOV_AMOUNT as col_2_0_, 
       movement0_.MOV_DESCRIPTION as col_3_0_, 
       movement0_.MOV_TAX_VALUE as col_4_0_, 
       movement0_.MOV_ACCOUNT_NUMBER as col_5_0_, 
       movement0_.MOV_ACCOUNT_AFDELING_ID as col_5_1_, 
       (select sum(movement2_.MOV_AMOUNT) from MOVEMENT movement2_ WHERE where movement2_.date > '20-02-2011') as col_6_0_, 
       account1_.KONTONR as KONTONR673_, 
       account1_.AFDELING_ID as AFDELING14_673_, 
       account1_.KON_FUNCTION as KON2_673_, 
       account1_.KON_BALANCE as KON3_673_, 
       account1_.KON_BLOCKED as KON4_673_, 
       account1_.KONTO_NAVN as KONTO5_673_, 
       account1_.KON_KEY_FIGURE_CODE as KON6_673_, 
       account1_.KON_MONITORED as KON7_673_, 
       account1_.KON_SUGGEST_CONTRA_ACC as KON8_673_, 
       account1_.KON_TOTALFROM as KON9_673_, 
       account1_.KON_TYPE as KON10_673_, 
       account1_.UAFD_GRP_NAVN as UAFD11_673_, 
       account1_.UAFD_NAVN as UAFD12_673_, 
       account1_.KON_TAX as KON15_673_, 
       account1_.OPTLOCK as OPTLOCK673_ 
from MOVEMENT movement0_ 
inner join KONTOPLAN account1_ 
      on movement0_.MOV_ACCOUNT_NUMBER=account1_.KONTONR 
      and movement0_.MOV_ACCOUNT_AFDELING_ID=account1_.AFDELING_ID 
group by movement0_.MOV_DATE , 
         movement0_.MOV_DOCUMENT_NUMBER , 
         movement0_.MOV_AMOUNT , 
         movement0_.MOV_DESCRIPTION , 
         movement0_.MOV_TAX_VALUE , 
         movement0_.MOV_ACCOUNT_NUMBER, 
         movement0_.MOV_ACCOUNT_AFDELING_ID

Has someone been faced the same problem? Is it a hibernate bug or I'm doing something wrong? Is there some workarround for it?

Thanks!