why does not work: select count(*) from (complex subquery)

snorbi · **Joined:** Fri Apr 02, 2004 9:57 am **Posts:** 14

How should I express the following SQL query with HQL?

select count(*) from (complex subquery)

Thanks,
Norbi

emmanuel · **Posted:** Fri Apr 02, 2004 1:53 pm

Subqueries are not allowed on the from clause AFAIK

baliukas · **Posted:** Fri Apr 02, 2004 2:11 pm

It is possible to use views for this use case (not all of RDBMS implementations support inline views too)

kinjaboy · **Posted:** Fri Apr 02, 2004 2:46 pm

MS SQL Server 2000(+), and Oracle 9i(+) currently support subqueries in the from clause. (MySQL will have this soon).

If you are just looking for a straight count, you could:
1. Grab the Connection from the Session and do it the old fashioned way.
2. Check out http://www.hibernate.org/118.html#A3

I'm sure there are other alternatives, but these work for me.

hope this helps