from subquery in Hibernate

domnappo · **Joined:** Mon Aug 31, 2009 7:24 am **Posts:** 7

Hi all,

I have a query with subselect and I found that Hibernate hasn't support for it.

Do you know how to correct (maybe after optimization:P) this query in HQL?

SELECT t1.greens, t2.oranges, t3.reds FROM
(SELECT count(t) as greens FROM tableX t WHERE t.validation="GREEN") t1,
(SELECT count(t) as oranges FROM tableX t WHERE t.validation="ORANGE") t2,
(SELECT count(t) as reds FROM tableX t WHERE t.validation="RED") t3

Table "tableX" is mapped correctly.

domnappo · **Joined:** Mon Aug 31, 2009 7:24 am **Posts:** 7

Sorry, I missed the following:

The exception raised by HQL parser is:

org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 23

mmerder · **Posted:** Wed Nov 11, 2009 9:08 am

Hibernate Reference wrote:

Note that HQL subqueries can occur only in the select or where clauses.

domnappo · **Joined:** Mon Aug 31, 2009 7:24 am **Posts:** 7

Hi mmerder,

thanks for your answer.

How could you re-write that query without using subselects?
I think that it's a quite common query to report counting on a table's column with different values.

mmerder · **Posted:** Mon Nov 23, 2009 5:11 am

Use native SQL or try to use a case-when.