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
– Darek, Commented May 18, 2016 at 11:09

Community · Accepted Answer · 2017-05-23 12:31:40Z

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, 2017 at 12:31

CommunityBot

11 silver badge

answered May 18, 2016 at 11:14

Darek

5703 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shiladittya Chakraborty Over a year ago

I don't want to use @NamedNativeQuery. I am using only namedQuery. How can I use the same query in namedquery?

Darek Over a year ago

@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...

Shiladittya Chakraborty Over a year ago

Is there any other way to achieve the listtag functionality in namedquery?

Darek Over a year ago

@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.

Roberto Benazzato · Accepted Answer · 2016-05-18 11:14:08Z

0

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.

answered May 18, 2016 at 11:14

Roberto Benazzato

4671 gold badge6 silver badges17 bronze badges

Collectives™ on Stack Overflow

Hibernate named query issue

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related