Issue
I have a column called PAYMENT_REF
that contains value of the following sequence:
DBC2020999999999999
It will always have the length 19
and starts with DBC
followed by the year which is this part: DBC2020
The following numbers after the year is a sequence number which is 999999999999
.
Basically I need to do a query that find the max value only from the sequence number ignoring the static DBC
and year 2020
.
Basically when I run the following query on oracle developer ide I get the correct result:
select MAX(SUBSTR(PAYMENT_REF,7,19))
from PAYMENT p
where PAYMENT_REF is not null;
So I tried the following query which looks should get the max value of:
public interface PaymentRepository extends JpaRepository<Payment, String> {
@Query(" select MAX(SUBSTRING(p.paymentRef,7,19)) from payment p where p.paymentRef is not null")
int getMaxRefNumber();
}
But I get a compilation error cannot resolve symbol: payment, any idea what I a missing here please?
Solution
I guess you should correct your query in the following way:
@Query("select MAX(SUBSTRING(p.paymentRef, 8, 12)) from Payment p where p.paymentRef is not null")
String getMaxRefNumber();
As it is stated in the documentation:
SUBSTRING
Extracts a portion of a string value. The second argument denotes the starting position, where 1 is the first character of the string. The third (optional) argument denotes the length.
1 8
| |
DBC2020999999999999
\ /
12 chars
P.S. To be honest I wasn't able to cast string to number in the hql.
The statement like the following:
select MAX( cast(SUBSTRING(p.paymentRef, 8, 12) as long) )
from Payment p where p.paymentRef is not null
will not work, the following exception is raced:
java.lang.IllegalArgumentException: Type specified for TypedQuery [java.lang.Long] is incompatible with query return type [class java.lang.String]
at org.hibernate.internal.AbstractSharedSessionContract.resultClassChecking(AbstractSharedSessionContract.java:863)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:817)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
Answered By - SternK