This HQL works when I run it...

select A.pdcid as PDC_ID, A.afterDate as ISSUE_DATE, count(A.pdcid) as NumRecord
from WrkTable A where (A.oneInd = 'B' or A.oneInd is null) and
A.packageId in (select B.packageId from SrcTable B where B.comp_id.fileId = 'file123')
group by A.pdcid, A.afterDate order by count(*)

but when I try to restrict the number of records the query above returns by filtering on one of its alias columns like this(the changes in RED)...

select PDC_ID, ISSUE_DATE, NumRecord from (select A.pdcid as PDC_ID, A.afterDate as ISSUE_DATE, count(A.pdcid) as NumRecord
from WrkTable A where (A.oneInd = 'B' or A.oneInd is null) and
A.packageId in (select B.packageId from SrcTable B where B.comp_id.fileId = 'file123')
group by A.pdcid, A.afterDate order by count(*)) where NumRecord>=2

I get the following error,

org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 43 [select PDC_ID, ISSUE_DATE, NumRecord from ( select A.pdcid as PDC_ID, A.afterDate as ISSUE_DATE, count(A.pdcid) as NumRecord from com.smt.sme.pqr.bom.wrk.WrkTable A where (A.oneInd = 'B' or A.oneInd is null) and A.packageId in (select B.packageId from com.smt.sme.pqr.bom.src.SrcTable B where B.comp_id.fileId = 'file123') group by A.pdcid, A.afterDate order by count(*)) where NumRecord>=2]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:425)


oh and for the record I tried this too, but wouldnt work..


select S.PDC_ID, S.ISSUE_DATE, S.NumRecord from (select A.pdcid as PDC_ID, A.afterDate as ISSUE_DATE, count(A.pdcid) as NumRecord
from WrkTable A where (A.oneInd = 'B' or A.oneInd is null) and
A.packageId in (select B.packageId from SrcTable B where B.comp_id.fileId = 'file123')
group by A.pdcid, A.afterDate order by count(*)) S where S.NumRecord>=2

Sorry, but subqueries in FROM clause are not allowed in Hibernate -- only in SELECT & WHERE clauses.

Thanks for providing the solution.....I achieved the filtering by using plain old Java....

tut...tut...I see this inability to add sub-queries in the FROM clause as a big short-coming of Hibernate and of course I have to rewrite that HQL query anways....

It's not allowed in HQL, but native SQL works.

If fact, one of our tricks when we can't really get a unique key in hibernate because of outer joins in report queries is to do the following: