Win a copy of Murach's Python Programming this week in the Jython/Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

problem with request hql  RSS feed

 
hana jenhani
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi i WOULD LIKE to do a request to recuperate a list of number of tickets from the database by status(i have an entity ticket that has an attribut status (type String) ,this status can has different values('invalid'-'resolved'-'closed'-'new') i has made 4 request for every type and it works but i would like to have all the number of each type of status in one list
i has tried to work with case when but it doesn't work

please help
 
Umesh Miie
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Give it a try in the following way:

SELECT SUM(CASE WHEN ticket.STATUS = Invalid THEN 1 ELSE 0 END) invalidCount, SUM(CASE WHEN ticket.STATUS = New THEN 1 ELSE 0 END) newCount
FROM TicketEntity ticket where ticket.dateCreation between :debut and :fin

I remember reading about a bug where the entityManager.createQuery do not recognize COUNT CASE statements.
 
hana jenhani
Greenhorn
Posts: 18
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i tried it but i have got the following exception java.lang.IllegalArgumentException : org.hibernate.hql.ast.QuerySyntaxException: unexpected token: invalidCount near line 1, column 65 [SELECT SUM(CASE WHEN ticket.Status = Invalid THEN 1 ELSE 0 END) invalidCount, SUM(CASE WHEN ticket.Status = New THEN 1 ELSE 0 END) newCount FROM com.entities.ticket.TicketEntity ticket where ticket.dateCreation between :debut and :fin]
null
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!