Please convert this SQL query to hibernate

nandhakumar · **Joined:** Thu May 06, 2010 6:35 am **Posts:** 3

Hi Guys,

Please help me to write this SQL query to HQL..

Code:

SELECT acc.*, t.total FROM account as acc
JOIN (SELECT tx.msisdn as msisdn, sum(tx.amount) as total FROM accrued_transaction tx WHERE tx.year = 2010 AND tx.quarter = 2 AND (tx.opted_in is null OR tx.opted_in = 1) GROUP BY tx.msisdn) t ON t.msisdn = acc.msisdn
WHERE acc.account_group = 300 
and acc.opt_in_date is null

Thanks in advance

nandhakumar · **Joined:** Thu May 06, 2010 6:35 am **Posts:** 3

i have written like this

Code:

select acc, sum(tx.amount) FROM Account acc
         "INNER JOIN AccruedTransaction tx on acc.msisdn = tx.account.msisdn WHERE 
acc.group = :group and acc.optInDate is null and tx.year <= :year AND tx.quarter <= :quarter AND (tx.optedIn is null OR tx.optedIn = :lapsedOrOptedIn) GROUP BY tx.msisdn, acc.msisdn"

but it gives the error exactly at the ON class, please some one help me to solve this

smithdwsn · **Joined:** Mon May 24, 2010 2:47 pm **Posts:** 3

I think you should check class. There is wrong relationship given. So problem is created. And you should also check parameters.