tags:

views:

467

answers:

2

Hi

I have the following SQL query:

select c.id
from (select id from customers) c

This query has no practical value - I simplified it greatly for the purpose of this post.

My question: is it possible have a subquery in the from clause using HQL. If not, can I perhaps query the customers first, kinda like a temp table in sql, and then use the result as the source of the next query?

thanks

+1  A: 

Yes, it's possible.

The query above can be written in HQL as:

select Id
from Customer
where Id in (select Id from Customer)
Diego Mijelshon
I tried it, but it does not work. However, when I change the subquery it works fine
MegaByte
Sorry, you're right. It does support subqueries, but not all of them. I'm changing my answer.
Diego Mijelshon
Yes, that seems to work fine - is there another solution. I dont like the IN clause - what happens if the customers table contains a 1000 entries...you will probably run into some memory issues.
MegaByte
That probably won't happen with your actual query (the one above doesn't make much sense). Wanna post something closer to the real one?
Diego Mijelshon
A: 

I've run into this issue myself. Took me a while to realise that hql does not support subqueries in the from clause.

See section 14.13 in the hql documentation here.

Jonathan Moffatt