CODEWITHSUNDEEP
javahibernate
Shiladittya Chakraborty
Shiladittya Chakraborty

Reputation: 4418

Hibernate named query issue

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?

Upvotes: 0

Views: 548

Answers (2)

Darek
Darek

Reputation: 570

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

Upvotes: 1

Roberto Benazzato
Roberto Benazzato

Reputation: 467

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.

Upvotes: 0

Related Questions