I need to take values from a database and put them into a List<Long>
. I made a native query but it doesn’t work correctly. Here’s a picture of the column on my database:
I need to count the same statuses (“W” and “I” represent the same status but are represented by another letter). I prepared a query as shown below:
JavaScript
x
@Query(value = "SELECT COUNT(CASE WHEN table.status = 'W' OR table.status = 'I' THEN 1 ELSE 0 END) AS WI,"
" COUNT(CASE WHEN table.status = 'S' THEN 1 ELSE 0 END) AS S," +
" COUNT(CASE WHEN table.status = 'N' THEN 1 ELSE 0 END) AS N," +
" COUNT(CASE WHEN table.status = 'D' THEN 1 ELSE 0 END) AS D," +
" COUNT(CASE WHEN table.status = 'E' THEN 1 ELSE 0 END) AS E," +
" COUNT(CASE WHEN table.status = 'T' THEN 1 ELSE 0 END) AS T" +
" FROM table.table table" +
" WHERE table.id = :id"+
" GROUP BY table.status" +
" ORDER BY table.status ASC",
nativeQuery = true)
List<Long> query1 (Long id);
But it’s not working correctly. When I run the query I get the following result:
But it should be {2(D),2(E),1(N),1(S),1(T),3(W and I). Do you have any idea on how to fix it?
Advertisement
Answer
This query returns what you want:
JavaScript
select count(*) from (
SELECT CASE WHEN status = 'W' OR status = 'I' THEN 'WI' else status end as status
FROM @table
) as a
group by status
order by status
So maybe this is the code you require (you have very complex table names):
JavaScript
@Query(value = "select count(*) from (" +
" SELECT CASE WHEN table.status = 'W' OR table.status = 'I' THEN 'WI' else table.status end as status" +
" FROM table.table table " +
" WHERE table.id = :id"+
" ) as a " +
" group by status" +
" order by status",
nativeQuery = true)
List<Long> query1 (Long id);