CODEWITHSUNDEEP
mysqlhibernatepostgresqlhqlhibernate-4.x
Develope001
Develope001

Reputation: 41

Using CAST(columnName AS INT) in HQL

I'm using Hibernate 4 and Postgres

The following query is a join of two tables where I need to cast a column that is varchar to integer for retrieving certain values in sequential order. Does HQL support this? THe documentation says so but I get exception as below

Query query  = session.createQuery("SELECT DISTINCT a.attributeName, a.displayName, CAST(a.seqNo AS INTEGER) FROM Entitydefinitionconcrete a, Entitymasterconcrete b WHERE (a.entityTypeId=b.concreteEntityId AND b.baseType=:baseType) AND a.viewGrid=:flag ORDER BY CAST(a.seqNo AS INTEGER)");
query.setParameter("baseType","test");
query.setParameter("flag", true);
query.list();

Exception : 

HHH000203: processEqualityExpression() : No expression to process!
org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '.' near line 1, column 70 [SELECT DISTINCT a.attributeName, a.displayName, CAST(a.seqNo AS Types.INTEGER) FROM com.tcs.oss.tims.common.entityBeans.Entitydefinitionconcrete a, com.tcs.oss.tims.common.entityBeans.Entitymasterconcrete b WHERE (a.entityTypeId=b.concreteEntityId AND b.baseType=:baseType) AND a.viewGrid=:flag ORDER BY CAST(a.seqNo AS INTEGER)]
     at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:79)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:278)
   at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)

Upvotes: 1

Views: 12843

Answers (1)

tharindu_DG
tharindu_DG

Reputation: 9291

The DOC says, 

cast(... as ...), where the second argument is the name of a Hibernate type, and 
extract(... from ...) if ANSI cast() and extract() is supported by the underlying database

E.G : cast(propName as integer)

Upvotes: 3

Related Questions