deve_thomos
asked on
how to restict duplicate values ?
Hi expert, below i have query but i am getting 9 times out put. but it should come 3 times only.
out put i attached here.
select a.relationship_name,a.obligor,a.facility_name_obg,a.Rating_Type,rp.fcrp_business_group,rp.fcrp_quarter,rp.review_type,rp.entity_name,
(select description from ms_fcr_managed_geography g where g.managed_geography_id=rel.relationship_region) as region,
decode(a.rating_type,'FRR',(SELECT facility_OSUC_PSC FROM TABLE(SELECT ms_fcr_brp_clob.MS_FCR_FACILITY_CLOB(FACILITY_CLOB)
FROM ms_fcr_linesheet_exec_obg where task_object_id = b.task_object_id)),'ORR',c.obligor_osuc_pse,'RRR/OLR',NULL) exposure,
decode(a.rating_type,'FRR',(SELECT of_subgrades FROM TABLE(SELECT ms_fcr_brp_clob.MS_FCR_FACILITY_CLOB(FACILITY_CLOB)
FROM ms_fcr_linesheet_exec_obg where task_object_id = b.task_object_id)) ,'ORR',c.OF_SUBGRADES_OBLIGOR,'RRR/OLR',b.of_subgrades) of_subgrades,
decode(a.rating_type,'FRR',(SELECT frr FROM TABLE(SELECT ms_fcr_brp_clob.MS_FCR_FACILITY_CLOB(FACILITY_CLOB)
FROM ms_fcr_linesheet_exec_obg where task_object_id = b.task_object_id)) ,'ORR',c.orr,'RRR/OLR',concat(concat(b.REL_RR,' , '), b.REL_OLR)) from_dt,
decode(a.rating_type,'FRR',(SELECT new_frr FROM TABLE(SELECT ms_fcr_brp_clob.MS_FCR_FACILITY_CLOB(FACILITY_CLOB)
FROM ms_fcr_linesheet_exec_obg where task_object_id = b.task_object_id)) ,'ORR',c.new_orr,'RRR/OLR',concat(concat(b.NEW_REL_RR,' , '), b.NEW_REL_OLR)) to_dt,
decode(a.rating_type,'FRR',(SELECT risk_rating_opinion FROM TABLE(SELECT ms_fcr_brp_clob.MS_FCR_FACILITY_CLOB(FACILITY_CLOB)
FROM ms_fcr_linesheet_exec_obg where task_object_id = b.task_object_id)) ,'ORR',c.RISK_RATING_OPINION_OBLIGOR,'RRR/OLR',concat(concat(b.RISK_RATING_OPINION,' , '), b.RISK_RATING_OPINION_OLR)) rating
from (select lnx.relationship_name,lno.obligor,lno.facility_name_obg,
CASE when lnx.relationship_name is not null and lno.obligor is not null and lno.facility_name_obg is not null
then 'FRR'
when lnx.relationship_name is not null and lno.obligor is not null
then 'ORR'
else 'RRR/OLR'
end as Rating_Type
from ms_fcr_linesheet_exec lnx,ms_fcr_linesheet_exec_obg lno where lnx.task_object_id = lno.task_object_id
and lnx.task_object_id = 'CAD-CCB-BRR-2013-Q2-10058'
group by lnx.relationship_name, rollup(lno.obligor,lno.facility_name_obg)) a,ms_fcr_linesheet_exec b,
ms_fcr_linesheet_exec_obg c ,ms_fcr_relationship rel,ms_fcr_linesheet ls,ms_fcr_review_plan rp
where a.relationship_name = b.relationship_name
and b.task_object_id = c.task_object_id
and b.task_object_id = 'CAD-CCB-BRR-2013-Q2-10058'
and b.relationship_id = rel.relationship_id
And B.Srd_Title = Ls.Select_File_Srd
and ls.entity=rp.entity_id
out put i attached here.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html