Solved

How can I eliminate repeated values in my query?

Posted on 2008-10-08
6
201 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

'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 …
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…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
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 …

930 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

19 Experts available now in Live!

Get 1:1 Help Now