Solved

How can I eliminate repeated values in my query?

Posted on 2008-10-08
6
200 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 142

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 142

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 142

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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 video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now