Hi all,

I am trying to execute the following query in hql:

string query = "SELECT task FROM "
+ "Tasks as task INNER JOIN "
+ "(SELECT MAX(grpTask.CreationDate) as MaxDate,grpTask.Process.Id as ProcessId FROM Tasks AS grpTask "
+ "WHERE grpTask.Process.CreatedBy='" + userName + "' "
+ "GROUP BY grpTask.Process.Id) AS groupResult "
+ "ON (task.Process.Id=groupResult.ProcessId AND task.CreationDate=groupResult.MaxDate)";

but am getting the following exception:

{NHibernate.QueryException: in expected: SELECT [SELECT task FROM (SELECT MAX(grpTask.CreationDate) as MaxDate,grpTask.Process FROM TaskFlowDB.Tasks AS grpTask WHERE grpTask.Process.CreatedBy='test.john.doe' GROUP BY grpTask.process) AS groupResult INNER JOIN Tasks as task ON (task.Process=groupResult.Process AND task.CreationDate=groupResult.MaxDate)]
at NHibernate.Hql.Classic.FromParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ClauseParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.PreprocessingParser.Token(String token, QueryTranslator q)
at NHibernate.Hql.Classic.ParserHelper.Parse(IParser p, String text, String seperators, QueryTranslator q)
at NHibernate.Hql.Classic.QueryTranslator.Compile()
at NHibernate.Hql.Classic.QueryTranslator.Compile(IDictionary replacements, Boolean scalar)
at NHibernate.Impl.SessionFactoryImpl.GetQuery(String queryString, Boolean shallow, IDictionary enabledFilters)
at NHibernate.Impl.SessionImpl.GetQueries(String query, Boolean scalar)
at NHibernate.Impl.SessionImpl.Find(String query, QueryParameters parameters, IList results)
at NHibernate.Impl.SessionImpl.Find[T](String query, QueryParameters parameters)
at NHibernate.Impl.QueryImpl.List[T]()

any idea what could be the problem with the hql query?

thanks in advance.