Hi All,
I am trying to convert the following native query(oracle) to a Hibernate one. I am having trouble getting the sum with coalesce to work in hibernate, not able to get the syntax right. Can some one help?
SELECT
ri.report_definition_id
, ri.report_definition_nm
, ri.report_instance_id
, count(distinct rei.entity_row_id) total_entity_rows
, sum(nvl(( select distinct 1 from tbl_user_role where erlp.role_id is null OR exists (select role_id from tbl_user_role ur where ur.role_id=erlp.role_id and ur.user_id=:usid)),0)) TOT_ALLOWED_ACCESS
from TBL_RG_REPORT_INSTANCE ri left outer join TBL_RG_REPORT_ENTITY_INST rei on ri.report_instance_id=rei.report_instance_id
and ri.tid=rei.tid
left outer join TBL_ENTITY_ROW er on rei.tid = er.tid
and rei.entity_id=er.entity_id
and rei.entity_row_id=er.entity_row_id
left outer join tbl_entity_row_level_perm erlp on er.tid = erlp.tid
and er.entity_id=erlp.entity_id
and er.entity_row_id=erlp.entity_row_id
left outer join tbl_entity_key_values ekv on
er.tid = ekv.tid
and er.entity_row_id=ekv.entity_row_id
and er.entity_id=ekv.entity_id
where
rei.comp_definition_id=0
group by ri.report_definition_id
, ri.report_definition_nm
, ri.report_instance_id
having count(distinct rei.entity_row_id) = sum(nvl(( select distinct 1 from tbl_user_role where erlp.role_id is null OR exists (select role_id from tbl_user_role ur where ur.role_id=erlp.role_id and ur.user_id=:usid)),0))