?
Solved

How can I eliminate repeated values in my query?

Posted on 2008-10-08
6
Medium Priority
?
209 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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