RE: SQL help needed

From: Ken Naim <kennaim_at_gmail.com>
Date: Mon, 10 Jul 2006 13:02:44 -0500
Message-ID: <008401c6a44b$0b6a0dc0$b4ae6a44@KenHome>

Ignore my last email as I misread yours as I didn't consider that a long sql statement. The only other way would be to encompass that logic in a function but you would be adding a lot of overhead into the query due the context switches even if you made it deterministic.

Ken Naim

-----Original Message-----
From: Ken Naim [mailto:kennaim_at_gmail.com] Sent: Monday, July 10, 2006 12:59 PM
To: 'davidb158_at_hotmail.com'; 'mwf_at_rsiz.com'; 'oracle-l_at_freelists.org' Subject: RE: SQL help needed

Just add one in.

SELECT name, SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1   WHEN GROUPS LIKE 'GROUP B%' THEN 2
  WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) group_value FROM test
GROUP BY name
HAVING SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1

	WHEN GROUPS LIKE  'GROUP B%' THEN 2
	WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
= (SELECT SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
	WHEN GROUPS LIKE  'GROUP B%' THEN 2
	WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) 
	FROM test WHERE name ='Marry');

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Boyd
Sent: Monday, July 10, 2006 12:33 PM
To: mwf_at_rsiz.com; oracle-l_at_freelists.org Subject: RE: SQL help needed

Hi Mark,

Excellent. That's the answer I wanted. Thank you so much.

Do you know if there is any way that I can use an alias for sum clause? Otherwise the query is long as following:

SELECT name, SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1   WHEN GROUPS LIKE 'GROUP B%' THEN 2
  WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) FROM test GROUP BY name
HAVING SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1

	WHEN GROUPS LIKE  'GROUP B%' THEN 2
	WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END))
= (SELECT SUM((CASE WHEN GROUPS LIKE 'GROUP A%' THEN 1
	WHEN GROUPS LIKE  'GROUP B%' THEN 2
	WHEN GROUPS LIKE 'GROUP dev%' THEN 4 END)) 
	FROM test WHERE name ='Marry');

Dave

>From: "Mark W. Farnham" <mwf_at_rsiz.com>
>To: <davidb158_at_hotmail.com>, <oracle-l_at_freelists.org>
>Subject: RE: SQL help needed
>Date: Mon, 10 Jul 2006 12:09:31 -0400
>
>name group group_value
>M A 1
>M B 2
>M dev 4
>D etc.......
>
>
>select name, sum(group_value) from test
> group by name
> having group_value = (select sum(group_value) from test where name =
>'M'));
>
>should work (off of the top of my head and my typing is not tested.)
>
>Notice those group_values are disjoint powers of two. You could craft a
>decode on your existing data to do the same thing. If you don't enforce
>uniqueness on name-group tuples this could generate false answers, and
>you'd
>have to make the statement more complicated.
>
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org]On
>Behalf Of David Boyd
>Sent: Monday, July 10, 2006 11:18 AM
>To: oracle-l_at_freelists.org
>Subject: SQL help needed
>
>Hi List,
>
>I was wondering if a SQL statement is possible for following query.
>
>The name of the table is test. It has following data:
>
>name group
>Marry group A 1-1
>Marry group B 1-2
>Marry group dev 1-1
>Ann group A 2-3
>Dave group A 2-3
>Dave group B 4-1
>Dave group dev 3-2
>
>I want to find out the names that are in the exact same groups as 'Marry'.
>The number in the group has to be ignored. For the above data, the query
>should return 'Dave' only since both Marry and Dave are in group A, group
>B,
>and group dev.
>
>Thanks for any input advance.
>
>Dave
>
>_________________________________________________________________
>Express yourself instantly with MSN Messenger! Download today - it's FREE!
>http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
>
>--
>http://www.freelists.org/webpage/oracle-l
>
>

---

Is your PC infected? Get a FREE online computer virus scan from McAfeeR Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 10 2006 - 13:02:44 CDT