Count (*) from subquery

dmora · **Joined:** Mon Sep 03, 2007 5:22 pm **Posts:** 4

Hi all, I'm trying to do a query to count the result of another query using HQL, but the system show a error message, this is the query and the error.

SELECT COUNT(*) FROM (SELECT AudienceMeter a JOIN FETCH a.parameterList AS pl JOIN FETCH pl.parameter AS p JOIN FETCH p.command AS cmd)

and the error is:
unexpected token: ( near line 1, column 24

The subquery alone work fine, but when I put "SELECT COUNT(*) FROM (SQ)" don't work.

The question is if hibernate don't support subquery in the FROM clause, somebody know, how can I count the result of another query using HQL in one query?.

Greetings.

smadarapu · **Joined:** Thu Jan 12, 2006 4:35 pm **Posts:** 13

Probably you need to give an alias to the sub query, like

select count(*) from (subquery) as sq

dmora · **Joined:** Mon Sep 03, 2007 5:22 pm **Posts:** 4

I have tried with things like:

select count(*) from (subquery) as alias
select count(*) from (subquery) alias

And don´t work.
I think hibernate don´t support subquerys in the from clause.

smadarapu · **Joined:** Thu Jan 12, 2006 4:35 pm **Posts:** 13

I vaguely remember something about the JOIN FETCH, remove the word "FETCH" and try. That is if you are using the latest hibernate version(3.2.0CR).

saugata · **Joined:** Thu May 17, 2007 5:29 am **Posts:** 13

http://www.hibernate.org/hib_docs/reference/en/html/queryhql.html
14.16. Tips & Tricks

Quote:

You can count the number of query results without actually returning them:

Code:

( (Integer) session.iterate("select count(*) from ....").next() ).intValue()

Try changing the query to

Code:

SELECT COUNT(*) FROM AudienceMeter a JOIN FETCH a.parameterList AS pl JOIN FETCH pl.parameter AS p JOIN FETCH p.command AS cmd

dmora · **Joined:** Mon Sep 03, 2007 5:22 pm **Posts:** 4

With this select the erros is:
<ERROR: column "parameterl1_.audience_meter_id" must appear in the GROUP BY clause or be used in an aggregate function>

hoeft · **Posted:** Thu Sep 06, 2007 2:20 am

I remember that subselects aren't possible in the from clause in HQL/JPA-QL queries. I think you can use them only in the where part. Hence your statement can't work.

Further I don't know why you should left join fetch your collections when you don't need them: you are only interested in the amount of entities. The amount doesn't change whether you left join your colletions or not. So the following HQL/JPA-QL query is simpler and should work:

Code:

SELECT COUNT(*) FROM AudienceMeter am

dmora · **Joined:** Mon Sep 03, 2007 5:22 pm **Posts:** 4

I have tried that, but neither work correctly the count is not right, now I have solved the problem by this way:

- I get the select from Hibernate translated to Postgres SQL.
- Then I change this select from my requirements in Postgres SQL.
- Then I translated the Postgres SQL select to HQL select.

And the result is here:

" SELECT COUNT(*) from AudienceMeter a, AudienceMeterParameter amp, Parameter p, Command c WHERE a.id = amp.pk.audienceMeter.id AND amp.pk.parameter.id = p.id AND p.command.id = c.id AND a.status <> 0 AND c.code = '" + cmdCode + "'"