[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How can I eliminate repeated values in my query?

Posted on 2008-10-08
6
Medium Priority
?
212 Views
Last Modified: 2011-10-19
Hi There,

I have a query which works correctly until I add my case (CASE WHEN mv.end_date is null THEN 'Yes' ELSE 'No' END) Terminal_Group_Active)  to the select list.  mv.end_date is null when active and has an actual date when enddated. What I am finding is the active records are displaying twice , once with  'Yes' and once with 'No'. Any idea why?

Thanks,
R

0
Comment
Question by:Rhonda Carroll
[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
  • 3
  • 3
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22671789
the CASE statement as such will not produce duplicates...
there must be some other part of the query (ie a GROUP BY)...

please clarify
0
 

Author Comment

by:Rhonda Carroll
ID: 22677086
Hi angellll,

My query is attached, with the case statment in the select, it returns 2 rows, if i comment it out and uncomment and mv.END_DATE is null, it will return one row.(These rows have identical info).
Thanks,
R
Query.doc
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22677112
what about:
SELECT     mh.mobile_id, 
 	    mv.INMARSAT_ISN, 
	    mh.CUSTOMER_CODE, 
	    mh.SDATE AS Start_Date, 
	    to_char(mh.EDATE, 'DD/MM/YYYY') AS End_Date, 
	    SSM2.FIRST_name || ' ' || ssm2.last_name AS Sales_Manager, 
		mv.terminal_group_code, 
		tg.terminal_group_name ,
		tgv.contract_code ,
		MAX(CASE WHEN mv.end_date is null THEN 'Yes' ELSE 'No' END) Terminal_Group_Active,
		cs.AGENT_S_ACC_CODE AS AR_Code 
froM  	mobile_history mh, 
		CSS_TERM_GROUP_MOBILE_ID_MV mv, 
		mobile m, 
		cSCS_NLR_TERMINAL_GROUPS@DBLINK_CSS_CSSRO tg, 
		cSCS_NLR_company_CONTRACTS@DBLINK_CSS_CSSRO cc, 
		CSCS_SALES_SUPPORT_MANAGERS@DBLINK_CSS_CSSRO ssm1, 
		CSS_TERMINAL_GROUPS_V tgv,
		custsrvs cs,
		cSCS_SALES_SUPPORT_MANAGERS@DBLINK_CSS_CSSRO ssm2  
WHERE 	SYSDATE BETWEEN sdate AND edate 
		--and mv.END_DATE is null
		AND inmarsat_imn = m.mobile_id 
		AND mh.mobile_id = m.mobile_id 
		AND mv.TERMINAL_GROUP_CODE = tg.TERMINAL_GROUP_CODE
		and tg.TERMINAL_GROUP_CODE = tgv.TERMINAL_GROUP_CODE 
		and mh.MOBILE_Id = mv.INMARSAT_IMN	
		AND CS.CUSTOMER_CODE = MH.CUSTOMER_CODE (+) 				 
		and ssm1.SUPPORT_MANAGER_ID=tg.SALES_SUPPORT_ID
		and ssm2.SUPPORT_MANAGER_ID=tg.SALES_SUPPORT_MANAGER_ID
		AND mh.MOBILE_ID IN ('463790738')
GROUP BY mh.mobile_id, 
 	    mv.INMARSAT_ISN, 
	    mh.CUSTOMER_CODE, 
	    mh.SDATE, 
	    to_char(mh.EDATE, 'DD/MM/YYYY'), 
	    SSM2.FIRST_name || ' ' || ssm2.last_name , 
		mv.terminal_group_code, 
		tg.terminal_group_name ,
		tgv.contract_code ,
		cs.AGENT_S_ACC_CODE 
				

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rhonda Carroll
ID: 22677134
That worked great! But why did it need the max function and the group by?

R
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22677143
because  CASE WHEN mv.end_date is null THEN 'Yes' ELSE 'No' END) Terminal_Group_Active  must have returned 2 distinct values...
0
 

Author Closing Comment

by:Rhonda Carroll
ID: 31504358
Thanks again for your help!!
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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

656 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