Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

java hql not parsing sum(distinct property) and case when

 
nilesh pitale
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using java 1.5 and hibernate 3.0

I used in hql is sum(distinct property) and case when then but it's not parsing by Session.createQuery(query).

is there another way to use it in java
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch.

Please post the exact error message as well as the full HQL statement.
 
nilesh pitale
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Below for sum(distinct property)

select a.agentId,a.agentId,a.agentId,a.agentId,a.personName,t.name,count(distinct CD.callDetailId),sum(distinct CD.scored)/count(distinct CD.scored),a.quota,sum(distinct CD.scored)-a.quota,round(avg(SCR.scoredPercentage)) from net.voicelog.callRecording.entities.CallDetail CD left Outer join CD.agent as a left Outer join CD.agent.teams as t left outer join CD.agent.scoreCardResults as SCR where CD.clientId=a.client.clientId and a.status<>'D' and CD.client.clientId = :number0 group by a.personName,t.name,a.quota,a.agentId order by CD.agent.familyName asc, CD.agent.givenName asc, t.name asc, count(distinct CD.callDetailId) asc


org.hibernate.hql.ast.QuerySyntaxException: unexpected token: distinct
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)





Below For case when then

select a.agentId,a.agentId,a.agentId,a.agentId,a.personName,t.name,count(distinct CD.callDetailId),count ( distinct case when SCR.scoreStatus<>'D' then SCR.scoreCardResultId else null end),a.quota,count ( distinct case when SCR.scoreStatus<>'D' then SCR.scoreCardResultId else null end)-a.quota,round(avg(SCR.scoredPercentage)) from net.voicelog.callRecording.entities.CallDetail CD left Outer join CD.agent as a left Outer join CD.agent.teams as t left outer join CD.agent.scoreCardResults as SCR where CD.clientId=a.client.clientId and a.status<>'D' and CD.client.clientId = :number0 group by a.personName,t.name,a.quota,a.agentId order by CD.agent.familyName asc, CD.agent.givenName asc, t.name asc, count(distinct CD.callDetailId) asc


org.hibernate.hql.ast.QuerySyntaxException: unexpected token: case
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic