Help converting SQL query to JPQL

Question

I have a SQL query that I need to translate over into JPQL, and I'm beginning to wonder if this is one of the cases where I'll just have to use native SQL. For reference, here is the SQL query.

SELECT c.title, a.approval_total, r.requested_total
FROM
   codes c
INNER JOIN
 (SELECT code_id, year, SUM(requested_amount) requestedTotal
   FROM requests
   GROUP BY code_id, year) r
 ON c.id = r.code_id
INNER JOIN
 (SELECT code_id, year, SUM(approved_amount) approvedTotal
   FROM approvals
   GROUP BY code_id, year) a
 ON c.id = a.code_id
WHERE c.title = ? AND r.fiscal_year = ? and a.fiscal_year = ?

Does anyone know of a way to translate an in line view like these two into JPQL? Or alternatively a different way of structuring the query that might make it easier to translate?

Answer 1

I would first rewrite the SQL query without the use of inline selects

not sure if I write it correctly, but it would be something like this :

SELECT c.title, sum(a.approved_amount) as approval_total, SUM(r.requested_amount) as requested_total
FROM codes c
INNER JOIN requests r ON c.id = r.code_id
INNER JOIN approvals a ON c.id = a.code_id
WHERE c.title = ? AND r.fiscal_year = ? and a.fiscal_year = ?
GROUP By r.code_id, r.year, a.code_id, a.year