[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

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

0
Rhonda Carroll
Asked:
Rhonda Carroll
  • 3
  • 3
1 Solution
 
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
 
Guy Hengel [angelIII / a3]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
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.

 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now