native SQL to HQL problem, please help

KakashiSensei · **Joined:** Thu Aug 31, 2006 9:53 am **Posts:** 6

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

Hibernate version: 2.1.7

Name and version of the database you are using: PostGreSQL

Hi,

I have a (native PostGreSQL) SQL which looks like this:

Code:

select c1, c2, avg(c4) from 

(select c1, c2, c3, sum(c4) as c4 
  from Table 
  where <some condition>
  group by c1, c2, c3 
  order by c1, c2, c3)  as X

group by c1, c2

This code runs on PostGreSQL perfectly, but I dont have any idea (and tried a lot of attempts) how to translate this to HQL.

The problem starts when I try to do a SELECT after the FROM clausule. I have read the HQL tutorial and searched for similar problems on the forum, but couldnt find any solution.

So if someone could help me with this, I would really appreciate that.

Thanks in advance for any assistance.

CWitherow · **Posted:** Thu Aug 31, 2006 11:00 am

Hibernate doesn't support subqueries in the FROM clause -- only in SELECT or WHERE clauses. Your query might be a candidate for using native SQL support within Hibernate. See Session.createSQLQuery(...).

Curtis ...