HQL syntax error : 'NHibernate.Hql.Ast.ANTLR.QuerySyntaxException'

Question

As a beginner in HQL , I have a SQL query that I'm trying to transform into hql.

select * from (

   select *
   from CORRELATION_VUE
   where film = v_oldidfilm and FILM2 not in (
                           select c.idfilm
                           from cotes c
                           where idclient = v_idclient)
   order by CORRELATION desc
 
 )
 where rownum <= 3;

So in HQL I'm trying this :

ISession s = NHibernateHelper.GetCurrentSession();
ITransaction tx = s.BeginTransaction();
IQuery query = s.CreateQuery(
    @"select u from (
            select u from vueCorreliser u
                where u.film = :idfilm
                    and u.FILM2 not in (
                        select c.idfilm from cote c
                            where c.idclient = :idclient)
                order by u.CORRELATION desc)
        where rownum <= 3; ")
    .SetInt32("idfilm", idfilm)
    .SetInt32("idclient", idclient); 

IList<Film> result = query.List<Film>();
tx.Commit();
return result;

But I'm receiving a syntax error on the CreateQuery line.

What did I do wrong?

Thank you

Answer 1

Though I consider this a duplicate of this other question from you, here is a separate, more explicit answer here.

hql does not support sub-queries in from statement. (It supports them in other statements, such as in where conditions.) You have to rewrite your query without the sub-query in the from.

Your sub-query seems to be there only for limiting rows count. Remove the row limit from the query, and use .SetMaxResults(yourMaxRowCount) on the HQL query object instead.

There are no needs for the termination statement ; in HQL, and I do not know if it is supported. I think it is not, better remove it.

var query = s.CreateQuery(
    @"select u from vueCorreliser u
        where u.film = :idfilm
            and u.FILM2 not in (
                select c.idfilm from cote c
                    where c.idclient = :idclient)
        order by u.CORRELATION desc")
    .SetInt32("idfilm", idfilm)
    .SetInt32("idclient", idclient)
    .SetMaxResults(4);

That should fix the QuerySyntaxException.

By the way, your usage pattern of transaction is not safe. When using locally scoped transactions, always nest them in using for ensuring they are properly closed.

using (var tx = session.BeginTransaction())
{
    ...
    tx.Commit();
    return result;
}

Even in case of failure, the transaction will then always be disposed, which causes it to rollback if it were still ongoing.