Path: news.cambrium.nl!textnews.cambrium.nl!feeder2.cambriumusenet.nl!feed.tweaknews.nl!postnews.google.com!j35g2000vbl.googlegroups.com!not-for-mail From: Pankaj Newsgroups: comp.databases.oracle.misc Subject: Re: Help Needed Date: Wed, 11 Nov 2009 16:42:15 -0800 (PST) Organization: http://groups.google.com Lines: 117 Message-ID: References: <4adf44a4-f96c-4f3a-8b0e-3b7e9af528cd@x16g2000vbk.googlegroups.com> NNTP-Posting-Host: 205.181.240.194 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1257986535 3846 127.0.0.1 (12 Nov 2009 00:42:15 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Thu, 12 Nov 2009 00:42:15 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: j35g2000vbl.googlegroups.com; posting-host=205.181.240.194; posting-account=Z_-z9AoAAAAY7-4g7g4HhkN6T9YzCB4M User-Agent: G2/1.0 X-HTTP-Via: 1.0 nowbdcpx01b.statestr.com:80 (IronPort-WSA/5.6.2-102) X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.0.3705; InfoPath.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729),gzip(gfe),gzip(gfe) Xref: news.cambrium.nl On Nov 10, 8:16=A0pm, Geoff Muldoon wrote: > Pankaj says... > > > > > > > On Nov 10, 6:34=A0pm, Geoff Muldoon > > wrote: > > > In article > > @h2g2000vbd.googlegroups.com>, Pankaj says... > > > > > On Nov 10, 5:28=A0pm, Geoff Muldoon > > > > wrote: > > > > > Pankaj says... > > > > > > > I have this enhancement where I have to EXCLUDE all users with = roles < > > > > > > 1000 but keeping following in mind > > > > > > 1) role < 1000 and user should not be in any other role > > > > > > OR > > > > > > 2) role < 1000 and role BETWEEN 9000 and 9010 and user should n= ot be > > > > > > in any other role. > > > > > > These two conditions seem to clash with each other, everyone that= meets > > > > > the second criteria seem to have been covered by the first one. > > > > > > GM > > > > > Thanks Geoff. But user Moore becomes eligible for the first > > > > requirement (his role < 1000 and does not have any other role). and > > > > not for second (role < 1000 and does not have any role between 9000 > > > > and 9010). > > > > Moore currently only has a role of 45, so yes, he is eligible under t= he > > > first requirement. > > > > If Moore also had a role of 9001, he would NOT be eligible under the = FIRST > > > requirement (even though he would be eligible under the second > > > requirement) because the roles 9000-9010 are still part of "any other > > > role". > > > > What are you trying to actually get? =A0Users that have roles that ar= e only > > > between 0-1000 and 9000-9010? > > > > GM- Hide quoted text - > > > > - Show quoted text - > > > Geoff, > > > Thats exactly what seems to be the dilemma here. I would explain > > again. We have set of users > > > 1) with ONLY role < 1000 > > 2) with ONLY (role < 1000 and BETWEEN 9000 and 9010) > > > The requirement is to remove the users falling in above requirement, > > so following users would be excluded > > > Should be EXCLUDED > > > role =A0 User > > 23 =A0 =A0 James > > 24 =A0 =A0 James > > 90 =A0 =A0 James > > 23 =A0 =A0 Scott > > 9001 Scott > > > But following user's should NOT BE EXCLUDED as they also belong to > > other roles apart from what has been described in above two points. > > > Should be INCLUDED > > > role =A0 =A0 User > > 23 =A0 =A0 =A0 James1 > > 1002 =A0 James1 =A0 =A0--Should be included as it belongs to role other= than > > < 1000 and between 9000 and 9010 > > 9000 =A0 James1 > > 9000 =A0 Scott1 =A0 =A0 -- Should be included as it is only part of bet= ween > > 9000 and 9010 and is not < 1000 > > 1000 =A0 Brian1 > > 1006 =A0 Brian2 > > > I hope I was able to make myself more clearer. > > Create a sub-query: > > select user, > =A0 =A0 sum(case when > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0then 1 else 0 > =A0 =A0 =A0 =A0 end) as count_under_1000, > =A0 =A0 sum(case when > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0then 1 else 0 > =A0 =A0 =A0 =A0 end) as count_special_val, > =A0 =A0 count(role) as count_all > from > group by user > > Then select the records from this sub-query to match your logic based on > the numbers in those calculated value columns. > > GM > > GM- Hide quoted text - > > - Show quoted text - Geoff, this worked. Thanks a lot. Take care