how to max() on a cast (String as integer)

aquental · **Joined:** Thu Apr 13, 2006 3:50 pm **Posts:** 2

Does any one knows how to obtain a max() on a string column?

The query I wanto to execute:

Code:

select MAX(TO_NUMBER(DOCNUM)) FROM HELAS.ONL_HEA_IN

the column (docnum) is a string, if I query the max value of this column using HQL:

Code:

HibernateTemplate ht = new HibernateTemplate(this.sessionFactory);
List maxDocNun = ht.find("select max(hin.Id.Docnum) from OnlHeaIn hin");

it returns tha MAX() of a string column, but I want to get the maximum numeric value of this column.
I tried to CAST(), but it does not work on a select statement:

Code:

ht.find("select max(cast(hin.Id.Docnum as integer)) from OnlHeaIn hin")

Quote:

Exception in thread "main" java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.AggregateNode
\-[AGGREGATE] AggregateNode: 'max'
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'cast' {originalText=cast}
\-[EXPR_LIST] SqlNode: 'exprList'
+-[DOT] DotNode: 'onlheain0_.DOCNUM' {propertyName=Docnum,dereferenceType=4,propertyPath=Id.Docnum,path=hin.Id.Docnum,tableAlias=onlheain0_,className=br.seal.hibernate.vo.OnlHeaIn,classAlias=hin}
| +-[DOT] DotNode: 'onlheain0_.DOCNUM' {propertyName=Docnum,dereferenceType=2,propertyPath=Id.Docnum,path=hin.Id,tableAlias=onlheain0_,className=br.seal.hibernate.vo.OnlHeaIn,classAlias=hin}
| | +-[ALIAS_REF] IdentNode: '(onlheain0_.FILENAME, onlheain0_.DOCNUM, onlheain0_.STAT, onlheain0_.LAGER)' {alias=hin, className=br.seal.hibernate.vo.OnlHeaIn, tableAlias=onlheain0_}
| | \-[IDENT] IdentNode: 'Id' {originalText=Id}
| \-[IDENT] IdentNode: 'Docnum' {originalText=Docnum}
\-[IDENT] IdentNode: 'integer' {originalText=integer}

Hibernate version: 3.1
Spring version: 1.2.7
database: Oracle 9i

Mapping documents:

Quote:

<hibernate-mapping package="br.seal.hibernate.vo">
<class name="OnlHeaIn" table="HELAS.ONL_HEA_IN">
<composite-id class="OnlHeaInPK" name="Id">
<key-property column="FILENAME" name="Filename"
type="string" />
<key-property column="DOCNUM" name="Docnum" type="string" />
<key-property column="STAT" name="Stat" type="string" />
<key-property column="LAGER" name="Lager" type="string" />
</composite-id>

.........
</hibernate-mapping>

I accept any sugestions, HQL, Criteria, direct SQL,...

thanks

DWright · **Joined:** Wed Jul 07, 2004 2:00 pm **Posts:** 64

So what is wrong with your original approach, with max(to_number(...))?

If the column is a number, why is it mapped as a string? If you really need the column as a string, but want to treate the value as a number everywhere in your application, you can also create a custom type that translates between a string in the database and a number in java.

aquental · **Joined:** Thu Apr 13, 2006 3:50 pm **Posts:** 2

The column (DOCNUM) is a string column (database schema), but its value is to be interpreted as a number.

To insert a new row I need to assign to this column a numeric value (max()+1), but a max() on the numeric value of the column.

I want to execute a max(to_number(DOCNUM)) using hibernate.

If I execute a HQL query inside Spring/HIbernate:

Code:

HibernateTemplate ht = new HibernateTemplate(this.sessionFactory);
List last = ht.find("select max(hin.Id.Docnum) from OnlHeaIn hin");

It returns the max() on the string valaue of this column.
ex: for the values [1,6,8,9,14,35,67] max(hin.Id.Docnum) would return ´9´ instead of ´67´. If I put, in SQL, max(to_number(DOCNUM)) it returns 67.

How do I use Hibernate to give me this number, the max() on a numeric value of a string column?

I tried changing the column type in the hibername mapping from string to integer, but the code above it returned me the string max() on the column (´9´). Is there a way in HQL to return the max(to_number(DOCNUM)). Or any other way using hibernate.

Thanks,
AQuental

visruthcv · **Joined:** Thu Mar 29, 2012 8:16 am **Posts:** 5

(Visruth CV, visruthcv@gmail.com, +91-9895154767)

Hey, generallay a column which set as primary key will be an integer type and auto_increment.
I suggest you to change the column type as an integer type and try the following code
org.hibernate.Session session=new AnnotationConfiguration().configure().buildSessionFactory().openSession();
Criteria crit = session.createCriteria(Users.class);
crit.setProjection(Projections.max("userId"));
int highestUserId = (Integer) crit.uniqueResult();
System.out.println(highestUserId);

Always Happy to help you people..................