My SQL Query is:

select Q1.paAmount, Q2.pAmount
From
(select sum(pa.amount) paAmount,pa.BookingID paBookingid from package_acct pa, Groups g , Person_Group pg where ....
group by pa.BookingID
)Q1
Inner Join
(SELECT sum(p.amount) pAmount, p.BookingID pbookingid FROM `payments` p where p.ispaid=1 group by p.BookingID
)Q2
on Q1.paBookingid=Q2.pBookingID

So I'm selecting on results of 2 other selects innerjoin.It works in mysql.

I tried to use createSQLQuery. but it needs entity classes. My select result is 2 BigDecimal values. I couldnt use BigDecimal as entity classes.
What should I do(Im using hibernate 2)?

use raw jdbc or use hibernate 3

hi.. is it really possible make a criteria o HQL that return a non entity classes?? Can i write a quary that return any field of a table?

Could anyone write examples? Or tell me where i can read this...

thanks

Is there any way to change this query to HQL?

foxmarco - yes, read the docs for scalar queries.

sh_sadrai - probably

I could wite such a query with scalar SQLQuery.But I couldnt find any docs about HQL scalar queries.

But It seems in hibernate, scalar queries mean different.
I am looking for queries that I can join two selects like:

select Q1.paAmount, Q2.pAmount
From
(select sum(pa.amount) paAmount,pa.BookingID paBookingid from package_acct pa, Groups g , Person_Group pg where ....
group by pa.BookingID
)Q1
Inner Join
(SELECT sum(p.amount) pAmount, p.BookingID pbookingid FROM `payments` p where p.ispaid=1 group by p.BookingID
)Q2
on Q1.paBookingid=Q2.pBookingID

no - what you are doing there is a scalar query (its returning two basic values) and you are utilizing join with sub-queries. Not available in HQL.

just do it in native sql if that works for you.

I was just concerned about the speed.
But I tried the SQL query. It was pretty faster than HQL!

eh yes ? raw native sql does not return persistent objects it "just" return raw data.... use the right tool for the job, both worlds has merits.