Exception: org.hibernate.exception.SQLGrammarException: could not execute query SELECT MAX(cast(value,integer))

Question

I have a query:

<query name="getMaxId">
       <![CDATA[from  Identifier where value = (select MAX (cast(value, integer)) from Identifier where type = :type) ]]>
    </query>

where value is: <property name="value" type="java.lang.String" column="value" not-null="true"/>

and try to invoke it with:

Query q = getSession().getNamedQuery("getMaxId");
q.setString("type", type);
List<Identifier> results = q.list();

However I get an error:

Exception: org.hibernate.exception.SQLGrammarException: could not execute query Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

I also tried

<![CDATA[from  Identifier where value = (select MAX (to_number(value)) from Identifier where type = :type) ]]>

but then I receive

Caused by: java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode

Answer 1

Not the proper solution to this problem but kind of workaround is pure SQL instead of HQL:

createSQLQuery("SELECT MAX (to_number(value)) FROM ids");