0

When i am executing this code (JPA) 

   Query q=entityManager.createQuery(SELECT mc.account_no,mc.expiry_date,
         SUM(CAST(CONCAT(mc.sign,mc.balance) AS NUMBER)),mc.moneybank_id FROM muthutcardbalance mc
     WHERE mc.account_no='20' AND mc.scheme_code='MCB' AND mc.expiry_date >= '2015-07-28' 
 GROUP BY mc.expiry_date,mc.account_no ,mc.moneybank_id ORDER BY mc.expiry_date )

I get following error. Am struck with this.

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing the query [SELECT mc.account_no,mc.expiry_date,SUM(CAST(CONCAT(mc.sign,mc.balance) AS NUMBER)),mc.moneybank_id FROM muthutcardbalance mc WHERE mc.account_no='20' AND mc.scheme_code='MCB' AND mc.expiry_date >= '2015-07-28' GROUP BY mc.expiry_date,mc.account_no ,mc.moneybank_id ORDER BY mc.expiry_date ], line 1, column 49: unexpected token [(].

I'm using JPA 2 with EclipseLink 2.0.

What is wrong with my query?

But when removing this SUM(CAST(CONCAT(mc.sign,mc.balance) AS NUMBER)) it works fine

Janny
  • 681
  • 1
  • 8
  • 33
  • What happens if you change `SUM (CAST (CONCAT (mc.SIGN, mc.balance) AS NUMBER))` to `SUM(TO_NUMBER(mc.sign||mc.balance)) sum_balance`? Also, if mc.expiry_date is of datatype DATE then you should be using `and mc.expiry_date >= to_date('2015-07-28', 'yyyy-mm-dd')` instead - always be explicit when you're working with dates/timestamps; don't expect Oracle to guess what your date-as-a-string means! – Boneist Jul 28 '15 at 10:46
  • have you checked the query wether it works in a SQL editor (e.g. SQL Developer)? – PT_STAR Jul 28 '15 at 11:19

1 Answers1

0

createQuery expects a string as a parameter, so you should double quote the query string

entityManager.createQuery("select * from dual")
Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53