HQL function CAST ( ... AS ... ) in criteria?

Michael Baranov · **Joined:** Fri Apr 22, 2005 3:39 pm **Posts:** 18

Hello!

How can one use HQL function CAST ( ... AS ... ) in criteria queries?
For example I'm trying:

Code:

// ...
Restrictions.ge("cast(propName as integer)", 10);
// ...

It fails saying that property "cast(propName as integer)" can not be resolved.

Thanks!!!

Hibernate version: 3.2.0.rc2

Name and version of the database you are using:PostgreSQL 8.0

[/code]

anestikas · **Joined:** Tue Jun 24, 2008 8:24 am **Posts:** 4

Hi

I have exactly the same problem. Did you find a solution?

Can anyone else help?

Thanks

Cameron McKenzie · **Posted:** Tue Jun 24, 2008 9:57 am

What exactly are you trying to do here? Talk it out, and maybe we can find a better way. I think there is a better way.

anestikas · **Joined:** Tue Jun 24, 2008 8:24 am **Posts:** 4

well... i have a string property in a table and i need to compare it with an integer either with Restrictions.eq or Restrictions.between.

The property is string because i need to preserve the zeros in the beginning. I have already done this with queries using the "cast" function. Now i need to do it using criteria...

I don't know what else to tell you... :-(

I'm starting to think that it can't be done with criteria which is too bad since criteria are far better that simple queries. (at least when the queries are created dynamically)

Thank you

prashu50 · **Joined:** Sun Sep 07, 2008 11:07 pm **Posts:** 17

Hi,
I am having the same problem. Did you find any solution?
I have a varchar column in Database and I am trying to call max function
But I need to do max(to_number(column)) or can try with cast function

Please help

anestikas · **Joined:** Tue Jun 24, 2008 8:24 am **Posts:** 4

No I didn't find a solution.

I converted the field in the database to integer... and this is not a solution.

If you find a way then please post it here.

mseritan · **Posted:** Thu Nov 06, 2008 7:41 pm

A solution here is to:
1. define the field with formula.
2. use the postgres ::TYPE notation

For example in my case the statefp field is a character type and I would like to have it cast as integer. I define the field as the following

Code:

   @Formula(value="statefp::integer") 
   public Integer getFips() {
      return fips;
   }

I use it in a query as the following

Code:

criteria.add(Restrictions.lt("fips", new Integer(60)));

anestikas · **Joined:** Tue Jun 24, 2008 8:24 am **Posts:** 4

Thank you for this solution!

eremmel · **Joined:** Wed Mar 23, 2011 5:20 pm **Posts:** 3

Looking for Cast, I found also this page. But I also found this at Chapter 14, Expressions

Quote:

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