0

Is it possible to write the following query in jpql? I am presently forced to use entity manager's createNativeQuery method: This Query is of Oracle11g DB

SELECT TO_CHAR(t6.DCO,'DD-MM-YYYY'),
t0.UAP_DESCRIPTION,
COUNT(t0.UAP_ID)
FROM TRAXN t6,
TRAXN_LINE t5,
EXGN_OBJECT_TRAIL t4,
COUPON t3,
PACK_HEADER t2,
COUPON_BATCH_HEADER t1,
PARTNER_PRODUCT t0
WHERE ((((((t3.CPN_PARTNER = 205)
AND (t3.CPN_IS_REDEEMED    = 1))
AND (t5.TRAXN_DEBIT        = 0))
AND (t3.CPN_PARTNER_SERIAL = t2.BOX_SERIAL))
AND (t6.DCO BETWEEN TO_DATE('01-07-2014', 'DD-MM-YYYY') AND (TO_DATE('31-07-2014', 'DD-MM-YYYY')         +1)))
AND (((((t4.EO_TRAIL_TRANSITION_ID = t5.EO_TRAIL_TRANSITION_ID)
AND (t3.CPN_ID                     = t4.CPN_ID))
AND (t6.TRAXN_ID                   = t5.TRAXN_ID))
AND (t1.CPNB_ID                    = t2.CPNB_ID))
AND (t0.UAP_ID                     = t1.UAP_ID)))
GROUP BY TO_CHAR(t6.DCO,'DD-MM-YYYY'),
t0.UAP_DESCRIPTION
ORDER BY TO_CHAR(t6.DCO,'DD-MM-YYYY'),
t0.UAP_DESCRIPTION

The Problem is with the TO_CHAR clause in the select clause.

Kuldeep S Chauhan
  • 252
  • 2
  • 18
  • will [this](http://stackoverflow.com/questions/22804010/select-specific-date-format-in-jpa-criteria-query) help you – Ankur Singhal Aug 22 '14 at 11:34
  • JPA does not have support for TO_CHAR function as this is Oracle-specific function. If you write this query in JPQL there is no any gain here because if you want to switch database platform e.g. to PostgreSQL, you have to fix this query. – Magic Wand Aug 22 '14 at 11:46
  • @ankur-singhal I haven't user criteria query ever. I suppose it would be very difficult to build a criteria query for above query. – Kuldeep S Chauhan Aug 23 '14 at 05:10
  • @MagicWand I understand that that is the very reason I want too avoid writing native queries. – Kuldeep S Chauhan Aug 23 '14 at 05:12
  • @KuldeepSChauhan yo can find few tutorials to start with. – Ankur Singhal Aug 23 '14 at 06:38

0 Answers0