Solved

how to restict duplicate values ?

Posted on 2014-02-28
2
240 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

685 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