I'm attempting to write a complex HQL query that includes a subquery. I am using this query to fill an object(s). Here's my query.

"select new AppliedProductFieldCropHistory(grower.Name, farm.Name, field.Name, cropzone.Name, timeframe.Name, crop.Name, subcrop.Name, fieldcrophistory.Area, (from AppliedProduct as appliedproduct join appliedproduct.Application as application join application.ApplicationFields as appFields join appFields.FieldCrop as innerfieldcrophistory where innerfieldcrophistory.Id = fieldcrophistory.Id)) from FieldCropHistory as fieldcrophistory join fieldcrophistory.CropZone as cropzone join cropzone.Field as field join field.Farm as farm join farm.GrowerTimeFrame as growertimeframe join growertimeframe.Grower as grower join growertimeframe.TimeFrame as timeframe left outer join fieldcrophistory.Crop as crop left outer join fieldcrophistory.SubCrop as subcrop where fieldcrophistory.Id in (:fchs)"

You can see that my subquery starts at '(from AppliedProduct' and ends at 'fieldcrophistory.Id))'.

When I run this query I get this error message:
"aggregate function expected before (".

I'm not really sure that this type of subquering is possible in HQL...I haven't been able to find any complex examples on line...I wanted to use CreateCriteria to join several of these tables together, but apparently nHibernate doesn't yet support mulitple CreateCriteria's chained together in code.

Thanks ahead,

Shock56

I just found some information that explains to me why I cannot get this query to work. Apparently (and please correct me if I got some bad info) HQL does not currently support subqueries in the select clause, it only supports subqueries in the where clause. This is a major hinderence that is going to keep me from receiving results from the query in the manner that I want them. I'll have to create some intermediate objects besides the already generated poco objects I'm using. It's a bummer.

hibernate allows subqueries inside the select clause. i tried this and it worked with me:

select (subquery1) as foo, (subquery2) as bar from table where . . ..

but im facing the same problem when it comes to select new Foo(subquery)

if you found out a way arround it plz post the solution.