Please help me to convert this SQL to hibernate query

[Pattamuthu Aru]

select a.msisdn,sum(b.amount) 'amount'
from account a
inner join accrued_Transaction b on a.msisdn = b.msisdn
where a.account_group = 300 and a.opt_in_date is null and b.year <= 2010
AND b.quarter <= 2 AND (b.opted_in is null OR b.opted_in = 1)
GROUP BY b.msisdn, a.msisdn


HQL seems to be not accepting ON class

Thanks in advance

[Sue Temple]

If your sql works correctly, please ensure that you have the mapping classes right

Hql -

select a.msisdn, sum(b.amount) 
from Account as a inner join AccruedTransaction b
where a.msisdn = b.msisdn
  and a.group = 300 
  and a.optIn is null 
  and b.year <= 2010
  and b.qtr <= 2 
  and (b.optedIn is null or b.optedIn = 1) 
group by a.msisdn, b.msisdn

[Pattamuthu Aru]

i tried the same but it gave me exception

The named query in account

    
@NamedQuery(name="account.forLostSms", query="select acc, sum(tx.amount) FROM Account acc " +
			"INNER JOIN AccruedTransaction tx WHERE acc.msisdn = tx.account.msisdn AND " +
			"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"),	

And the exception is

   
13:57:10,927 ERROR [PARSER]  Path expected for join!
13:57:11,052 ERROR [PARSER]  Invalid path: 'tx.amount'
13:57:11,068 ERROR [PARSER]  Invalid path: 'tx.account.msisdn'
13:57:11,083 ERROR [PARSER]  right-hand operand of a binary operator was null
13:57:11,083 ERROR [PARSER] <AST>:0:0: unexpected end of subtree
13:57:11,099 ERROR [PARSER]  Invalid path: 'tx.year'
13:57:11,099 ERROR [PARSER] <AST>:0:0: unexpected end of subtree
13:57:11,115 ERROR [PARSER]  left-hand operand of a binary operator was null
13:57:11,130 ERROR [PARSER]  Invalid path: 'tx.quarter'
13:57:11,130 ERROR [PARSER] <AST>:0:0: unexpected end of subtree
13:57:11,130 ERROR [PARSER]  left-hand operand of a binary operator was null
13:57:11,146 ERROR [PARSER]  Invalid path: 'tx.optedIn'
13:57:11,161 ERROR [PARSER]  Invalid path: 'tx.optedIn'
13:57:11,161 ERROR [PARSER] <AST>:0:0: unexpected end of subtree
13:57:11,161 ERROR [PARSER]  left-hand operand of a binary operator was null
13:57:11,177 ERROR [PARSER]  Invalid path: 'tx.msisdn'
 

MY actual SQL query which it seems to work with out any error is like this, so if we able to convert this query in HQL, then it will be good

   
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
 

[Pattamuthu Aru]

Any suggestions or help, please.

Bringing to top

[Pattamuthu Aru]

Atleast someone please help me to convert this SQL query to Hibernate native query

   
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
 

i have just copy pasted this SQL in java but even then it doesnt work says

    
17:51:37,025 ERROR [PARSER] line 1:48: unexpected token: (
17:51:37,025 ERROR [PARSER] line 1:77: unexpected token: sum

Please thanks in advance

[Cj Recto]

There's no problem in your query, the problem is with your mapping file I had the same problem before when it comes to joining table.
this link solved my problem in mapping files for joined table.

java4s Join

see if it'll work for you.

[Mahendr Shinde]

You can try Native SQL Query if your SQL query works