Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to restict duplicate values ?

Posted on 2014-02-28
2
Medium Priority
?
246 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

715 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