Solved

how to restict duplicate values ?

Posted on 2014-02-28
2
243 Views
Last Modified: 2014-03-24
Hi expert, below i have query but i am getting  9 times out put. but it should come 3 times only.

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

Open in new window


 out put i attached here.
0
Comment
Question by:deve_thomos
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39894777
select distinct ....



or add additional join conditions so you don't get the multiplicative effect for matching multiple rows
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39894905
this article will explain where the issue comes from (how to detect it also), and hence how to troubleshoot it:
http://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question