EJB-QL exception

Question

select DISTINCT TOP 10 o.MaterialNumber from MaterialCreation_MDO o where o.MaterialGroup= ?1 and o.Noun= ?2 and o.MaterialNumber like ?3 order by o.MaterialNumber desc

this query is throwing exception on encountering 'TOP' keyword in ejb-ql statement....

Answer 1

TOP is unrecognized in EJB-QL, use LIMIT instead.

You can try the below modified query :

select DISTINCT o.MaterialNumber from MaterialCreation_MDO o where o.MaterialGroup= ?1 and o.Noun= ?2 and o.MaterialNumber like ?3 order by o.MaterialNumber desc LIMIT 10;

or alternatively you can give explicitly the number of results to be fetched by :

entityManager.createNativeQuery(query,YourClass.class).setParameter("name",value).setMaxResults(10).getResultList();

NamedQuery will also work fine.