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
