0

I got an exception when I deploy the following query on tomcat :

@Query("select max(cast(substring(r.reference,9,4) as decimal(4,0))) from RequestDbo r  where substring(r.reference,0,9) = :referenceRoot")
Long getMaxReference(@Param("referenceRoot") String referenceRoot);


The exeption is :

org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 54 [select max(cast(substring(r.reference,9,4) as decimal(4,0))) from RequestDbo r where substring(r.reference,0,9) = :referenceRoot]

Althoug the query is ok when executed directly on sql server.

I see no syntax error in my hql...

Any ideas ?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
Maxime
  • 168
  • 1
  • 9
  • Looks like your entire query is quoted by ORM with `[]`: `[select max(cast(substring(r.reference,9,4) as decimal(4,0))) from RequestDbo r where substring(r.reference,0,9) = :referenceRoot]` – Lukasz Szozda Jan 22 '16 at 08:45
  • The class contains other queries which are signed the same way and which are working well. example : @Query("select r from RequestDbo r") – Maxime Jan 22 '16 at 08:50

1 Answers1

0

Hibernate does not support this kind of casting as decimal(4,0) inside a JPQL query. You have four options as a workaround in here:

  1. Load your data to Java and then get the highest value;
  2. Use a Java class to cast the desired value and then call it's constructor inside the JPQL query;
  3. Use native SQL;
  4. Create a SQL function that cast the desired value and call this function inside the MAX of your query.

I also recommend you to take a look at this question this question. Good luck!

Community
  • 1
  • 1
Bonifacio
  • 1,482
  • 10
  • 19
  • 1
    Many thanks for your answer Bonifacio. The issue was caused by the reason you mentionned. I finaly fixed it in removing the CAST and parsing on the java side instead of in JPQL. – Maxime Jan 22 '16 at 13:15