HQL subselect feature

pyhavaim · **Joined:** Sun Oct 22, 2006 12:06 pm **Posts:** 39

Hi!

I need to retrieve some calculated information from columns,

Code:

select m.name, (SELECT sum(q.amont) from Balance q where q.Material=m.material)
From Material m

Now hql parser throws an exception beacause it assumes that ( is apart of some aggregate function).

I could do this with formula attribute, but since I need this formula only in one case, I don't want that nHibernate will execute this sub select on every other material retrieval.

I would like to submit this into jira but seems that jira site is down.

wolli · **Posted:** Wed Mar 12, 2008 6:15 am

I think subqueries in HQL only work in the from clause, not in the select. You could use a SQL query instead or maybe a join and a group by might work... something like

Code:

select m.name, sum(q.amont) from Balance q
right outer join q.Material
group by m.Name

Btw. JIRA is temporarilly moved to http://www.covalentsw.com:8080

pyhavaim · **Joined:** Sun Oct 22, 2006 12:06 pm **Posts:** 39

thanx,

I knew that I could write the HQL like this, the post query was kind a explanatory what I'd like to achieve. Actual requirement is more complicated than this is.

I don't know why this feature is disabled, maybe other sql servers won't support this :(

And now I wrote an sql query to deal this kind of complex calculation, but this feaure would be really cool to have.

ps. thanx for the jira tip!

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

wolli wrote:

I think subqueries in HQL only work in the from clause, not in the select.

As far as I know, it's the other way around:
they work in the select clause, but not in the from clause.
I am trying to find the jira issue for that later feature, to vote on it :)

wolli · **Posted:** Wed Jun 25, 2008 4:10 am

Sorry, meant "where" instead of "from". I've only seen subqueries in the where clause in HQL.

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

I created an issue for subselect in the from clause:
http://opensource.atlassian.com/project ... e/HHH-3356

wolli · **Posted:** Wed Jun 25, 2008 4:49 am

ge0ffrey wrote:

I created an issue for subselect in the from clause:
http://opensource.atlassian.com/project ... e/HHH-3356

You're aware of that that is the Hibernate JIRA not the NHibernate one !

ge0ffrey · **Joined:** Thu Oct 13, 2005 4:19 am **Posts:** 98

Yes :) I am using Hibernate, not Nhibernate. I wasn't aware this was the nhibernate forum. My mistake, sorry :)