Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3

Hi,

I try to create some basis SQL statements with the createQuery method.

-------- code --------------
Query countQuery = HibernateUtil.currentSession()
.createQuery("select count(*) from (select s.id as id, s.code as code, s.name as name from Service s) as tblCount");

------- Error ---------------

countQuery: select count(*) from (select s.id as id, s.code as code, s.name as name from Service s) as tblCount
11-nov-2005 21:28:55 org.hibernate.hql.ast.ErrorCounter reportError
SEVERE: *** ERROR: line 1:22: unexpected token: (

------ Test in MySQL -------
query: select count(*) from (select s.id as id, s.code as code, s.name as name from Service s) tblCount;

!!!! Same query except I remove the " as " for my table name

result: count(*) = 6


You see the query works in MySQL but not with the HSQL, somebody know where i'm wrong.

Thx in adv,
Pieter Pareit

createQuery in hibernate is looking for HQL. to use SQL use the createSqlQuery() method

I suggest you this two links: http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#queryhql-subqueries and http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#querycriteria-detachedqueries.

Hi !!!

Thx for the replies but it is not what i'm looking for.

I want to use HSQL! And also a subquery in my from clause.

select count(*)
from (
select s.id as id, s.code as code, s.name as name
from Service s
) as tblCount


But that doesn't work...

Grtz,
Pieter

Do you really need a subquery for this? If all you're doing is counting rows in table Service, why can't you do


???

Yeah, I know it looks stupid. But I'm creating a framework, and the framework creates a html navigatable table (next, previous).

I send the following query to my framework classes:

select s.id as id, s.code as code, s.name as name
from Service s

But i also want to display the record count, this is why I want to use this query.

select count(*)
from (
select s.id as id, s.code as code, s.name as name
from Service s
) as tblCount

I searched this forum and i found that it is
impossible. Because the subquery creates a list and not a resultset. I solved my problem by changing my framework in HTMLTable.addQuery() and HTMLTable.addCounterQuery().

Thanks for your help!
Pieter

i think that hibernate hql don't support subquery in from clause