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

Please add the complete stacktrace

Jens
– Jens

2023-01-17 13:14:02 +00:00
Commented Jan 17, 2023 at 13:14 — Jens
– Jens, Commented Jan 17, 2023 at 13:14
And the problem SQL.

jarlh
– jarlh

2023-01-17 13:34:36 +00:00
Commented Jan 17, 2023 at 13:34 — jarlh
– jarlh, Commented Jan 17, 2023 at 13:34

Michu93 · Accepted Answer · 2023-01-19 10:31:19Z

0

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");

answered Jan 19, 2023 at 10:31

Michu93

5,8578 gold badges61 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related