Solved

How can I eliminate repeated values in my query?

Posted on 2008-10-08
6
204 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
  • 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 125 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 

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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

828 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