How can I eliminate repeated values in my query?

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

Rhonda CarrollAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Rhonda CarrollAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Rhonda CarrollAuthor Commented:
That worked great! But why did it need the max function and the group by?

R
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
because  CASE WHEN mv.end_date is null THEN 'Yes' ELSE 'No' END) Terminal_Group_Active  must have returned 2 distinct values...
0
 
Rhonda CarrollAuthor Commented:
Thanks again for your help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.