Hibernate named query issue

Question

I have below name query

@NamedQuery(name="ScInstantTrack.getCustomerDetails", 
query="select b.cardDetail.mstCustomer.customerId, last_day(b.endDate), " +
        "LISTAGG(b.txnId,'|') WITHIN GROUP (ORDER BY b.endDate), " +
        "count(b.txnId), sum(b.amount), sum(b.balanceAmt), sum(b.redemptionAmt) " +
        "from ScInstantTrack b " +
        "where b.cardNo = b.cardDetail.cardBarcode " + 
        "AND b.cardDetail.mstCustomer.customerId = :customerId " + 
        "and b.startDate <= trunc(:todayDate) " + 
        "and b.endDate >= trunc(:todayDate) " + 
        "and b.cardDetail.mstStatus.statusId = 3003 group by b.cardDetail.mstCustomer.customerId, last_day(b.endDate)")

When I am executing this query then getting below error :

unexpected token: WITHIN

I am using Oracle Database.

Why I am getting this error? How to solve this issue?

First of all - show us whole query execution code; secondly - tell us which database is under the Hibernate hood (I'm guessing that Oracle)... You can also format pasted query - for readability. — Darek, May 18 '16 at 11:09

score 1 · Answer 1 · edited May 23 '17 at 12:31

1

Try to use @NamedNativeQuery instead of @NamedQuery.

Also check this explanation of difference between them.

Basically you are using expressions that are exclusive in Oracle DB. In other words - you want to execute native query (query in native for Oracle DB language). Named queries use Java Persistence Query Language (HQL i.e.).

edited May 23 '17 at 12:31

Community

1
1

answered May 18 '16 at 11:14

Darek

550
3
12

I don't want to use @NamedNativeQuery. I am using only namedQuery. How can I use the same query in namedquery? – Shiladittya Chakraborty May 19 '16 at 04:12
@ShiladittyaChakraborty If you have to use `@NamedQuery` you have to „translate” your native Oracle query to HQL/JPQL but, it may be difficult or, as someone mentioned, even impossible... – Darek May 19 '16 at 05:45
Is there any other way to achieve the listtag functionality in namedquery? – Shiladittya Chakraborty May 19 '16 at 06:07
@ShiladittyaChakraborty I haven't heard about sth like that in HQL but i doesn't mean that there's no equivalent at all. Try to search or create anoter question. – Darek May 19 '16 at 06:15

score 0 · Answer 2 · answered May 18 '16 at 11:14

The error happen because LISTAGG is an oracle specific function. That function is not avaliable in HQL and there is nothing you can use instead for HQL.

In order to get the result you have to use a SQLQuery wich perform native SQL queryes. This way You have to implement a version of thw query for each database, but it will work.

Hibernate named query issue

2 Answers2