CODEWITHSUNDEEP
javamysqlhibernatehql
Subhajit
Subhajit

Reputation: 894

Java Hibernate use of case when else query resulting exception in hql

Use of this query:

Need to get the minimum rank for the keywordIds where rank is not equal to 0 searchengine is "xyz.com" and, if a keywordId has no rank other than 0 then it should show as 10000

TrackId | KeywordId | SearchEngine | Rank

1 | 101 | xyz.com | 0

1 | 101 | xyz.com | 55

1 | 101 | xyz.com | 12

2 | 201 | xyz.com | 1

2 | 201 | xyz.com | 98

2 | 201 | xyz.com | 23

3 | 301 | xyz.com | 0

3 | 301 | xyz.com | 0

Result should be:

min Rank

12

1

10000

When I am using the hql query as below the result I am getting is getting exception :

bestRanks[i] = (List<Integer>)getHibernateTemplate().findByNamedParam("Select min(case when Rank > 0 then Rank else 10000 end) from Serpstrackhistory t where t.keywordId in (:KeywordId) and t.searchEngine=(:SearchEngine) group by KeywordId ORDER BY FIELD(KeywordId,t.keywordId in (:KeywordIds))",new String[]{"KeywordId", "SearchEngine", "KeywordIds"}, new Object[]{keywordId ,searchEngine[i], keywordId});

Exception:

java.lang.IllegalStateException: No data type for node: org.hibernate.hql.ast.tree.AggregateNode 
 \-[AGGREGATE] AggregateNode: 'min'
    \-[CASE] CaseNode: 'case'
       +-[WHEN] SqlNode: 'when'
       |  +-[GT] BinaryLogicOperatorNode: '>'
       |  |  +-[IDENT] IdentNode: 'Rank' {originalText=Rank}
       |  |  \-[NUM_INT] LiteralNode: '0'
       |  \-[IDENT] IdentNode: 'Rank' {originalText=Rank}
       \-[ELSE] SqlNode: 'else'
          \-[NUM_INT] LiteralNode: '10000'

When I am using the raw query as below the result I am getting is correct:

SELECT min(case when Rank > 0 then Rank else 10000 end) FROM `serpstrackhistory` WHERE  KeywordId in (1,2,3) and SearchEngine="xyz.com" group by KeywordId

Please help me to know what is incorrect in my hql query .

Upvotes: 0

Views: 564

Answers (1)

Arjit
Arjit

Reputation: 3456

Please try the query with aliases -

min(case when t.Rank > 0 then t.Rank else 10000 end)

Upvotes: 1

Related Questions