JPA + Hibernate + Named Query + how to JOIN a subquery result
- by Srihari
Can anybody help me in converting the following native query into a Named Query?
Native Query:
SELECT
usr1.user_id, urr1.role_id, usr2.user_id, urr2.role_id, usr1.school_id,
term.term_name, count(material.material_id) as "Total Book Count",
fpc.FOLLETT_PENDING_COUNT as "Follett Pending Count",
rrc.RESOLUTION_REQUIRED_COUNT as "Resolution Required Count"
FROM va_school sch
JOIN va_user_school_rel usr1 on sch.school_id=usr1.school_id
JOIN va_user_role_rel urr1 on usr1.user_id=urr1.user_id and urr1.role_id=1001
JOIN va_user_school_rel usr2 on sch.school_id=usr2.school_id
JOIN va_user_role_rel urr2 on usr2.user_id=urr2.user_id and urr2.role_id=1002
JOIN va_term term on term.school_id = usr1.school_id
JOIN va_class course on course.term_id = term.term_id
JOIN va_material material on material.class_id = course.class_id
LEFT JOIN
(SELECT VA_CLASS.TERM_ID as "TERM_ID", COUNT(*) as "FOLLETT_PENDING_COUNT"
FROM VA_CLASS JOIN VA_MATERIAL ON VA_MATERIAL.CLASS_ID = VA_CLASS.CLASS_ID
WHERE VA_CLASS.reference_flag = 'A' AND trunc(VA_MATERIAL.FOLLETT_STATUS) = 0
GROUP BY VA_CLASS.TERM_ID) fpc on term.term_id = fpc.term_id
LEFT JOIN
(SELECT VA_CLASS.TERM_ID as "TERM_ID", COUNT(*) as "RESOLUTION_REQUIRED_COUNT"
FROM VA_CLASS JOIN VA_MATERIAL ON VA_MATERIAL.CLASS_ID = VA_CLASS.CLASS_ID
WHERE VA_CLASS.reference_flag = 'A' AND trunc(VA_MATERIAL.FOLLETT_STATUS) = 1
GROUP BY VA_CLASS.TERM_ID) rrc on term.term_id = rrc.term_id
WHERE course.reference_flag = 'A'
GROUP BY usr1.user_id, urr1.role_id, usr2.user_id, urr2.role_id, usr1.school_id, term.term_name, fpc.FOLLETT_PENDING_COUNT, rrc.RESOLUTION_REQUIRED_COUNT
ORDER BY usr1.school_id, term.term_name;
Thanks in advance.
Srihari
