RE: SQL help needed

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@xxxxxxxxx] 
Sent: Monday, July 10, 2006 12:59 PM
To: 'davidb158@xxxxxxxxxxx'; 'mwf@xxxxxxxx'; 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of David Boyd
Sent: Monday, July 10, 2006 12:33 PM
To: mwf@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxx>
>To: <davidb158@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
>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@xxxxxxxxxxxxx 
>[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
>Behalf Of David Boyd
>Sent: Monday, July 10, 2006 11:18 AM
>To: oracle-l@xxxxxxxxxxxxx
>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


Other related posts: