Discussions

EJB programming & troubleshooting: ORDER-BY clause as parameter in EJB3?

  1. ORDER-BY clause as parameter in EJB3? (1 messages)

    I have a named native query which performs an aggregate calculation using GROUP BY returning one textual and three numerical columns, and it works fine. However, there is a requirement that the user should be able to decide on which of the numerical columns the result set should be sorted. How do I do that? This is the query named "overviewQuery": select holder_name as HOLDER, sum(case when status='NEW' then 1 else 0 end) as NO_OF_NEW, sum(case when status='ISSUED' then 1 else 0 end) as NO_OF_ISSUED, sum(case when status='FAILED' then 1 else 0 end) as NO_OF_FAILED, from HOLDER_EVENT group by holder_name order by :sortColumn; I've tried Query q = em.createNativeQuery("overviewQuery"); q.setParameter("sortColumn","NO_OF_NEW"); // or other columns but it doesn't work. I get no errors, it's like the parameter setting is just ignored. I use JBoss 4.2.2/MySQL 5.0.46 Any ideas? Thanks/Keijo
  2. Ok, me wrong. ORDER-BY cannot be a parameter (at jdbc level). Using nativeQuery instead.