CODEWITHSUNDEEP
javahibernate
user3767879
user3767879

Reputation: 43

finding max(column) where column is varchar type using hibernate query

Hi I have a column which is of type varchar like example:

1
2
3
4
5

I wanted to get the max value so i tried the following query 

select max(siAnswersId) from < table>

but this gives me wrong output so i tried using

MAX(CAST(siAnswersId AS INTEGER)) and it works fine when I run in sql developer

but when I frame the query in hibernate as

select MAX(CAST(SI_ANSWERS_ID AS INTEGER)) from < table>

it gives me below error

No data type for node: org.hibernate.hql.internal.ast.tree.AggregateNode 
<| \-[AGGREGATE] AggregateNode: 'MAX'<|    \-[METHOD_CALL] MethodNode: '('<|
       +-[METHOD_NAME] IdentNode: 'CAST' {originalText=CAST}<|
       \-[EXPR_LIST] SqlNode: 'exprList'<|
          +-[DOT] DotNode: 'serviceins0_.SI_ANSWERS_ID'
{propertyName=siAnswersId,dereferenceType=PRIMITIVE,getPropertyPath=siAnswersId,path
{synthetic-alias}.siAnswersId,tableAlias=serviceins0_,
className=com.hp.api.management.nfv.questionnair.entities.ServiceInstanceAnswersEntity,
classAlias=s}<|
      |  +-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}<|
      |  \-[IDENT] IdentNode: 'siAnswersId' {originalText=siAnswersId}<|
      \-[IDENT] IdentNode: 'INT' {originalText=INT}<|

Upvotes: 1

Views: 1431

Answers (2)

Greg Lindholm
Greg Lindholm

Reputation: 39

Actually the "int" must be lowercase as

MAX(CAST(siAnswersId AS int))

It doesn't work is INT, integer, or Integer. The message actually says "no data type" as it doesn't recognize these as a valid data type.

Upvotes: 2

TJ-
TJ-

Reputation: 14363

MAX(CAST(siAnswersId AS INT)) should do. INT instead of INTEGER.

Upvotes: 0

Related Questions