Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

how to restict duplicate values ?

Posted on 2014-02-28
2
Medium Priority
?
253 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
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

578 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