Hi Madhavan, You are always welcome. I had to write a similiar one some time back and I remember it was quite a task then. Thankfully I had a head start this time !. Hope you get the stuff sorted out. Glad to be of help ...
Regards, Jayadas -----Original Message----- Sent: Thursday, April 03, 2003 4:34 PM To: Multiple recipients of list ORACLE-L Hi Jaydas, Thanks for the reply. It gives me a good starting point to go with. The query handles cases where there are multiple rows. For eg: U=2006 has G=1010 and S=1013. If there were another U=2010 with same G=1010, then a rum through the query would generate a S=1012 for this combination also as the min checks for group_id in () and that will evaluate any U belonging to a single group that is part of the multiple groups that a U belongs to. But I will take this query as a starting point and will work on getting that resolved. Thanks for your time and appreciate your help Regards, Madhavan http://www.dpapps.com On Thu, 03 Apr 2003 12:28:42 -0800, "Chelur, Jayadas {PBSG}" <[EMAIL PROTECTED]> said: > Madhavan, > > I have created a similiar table and inserted the data > as follows :- > > ===================================================== > > CREATE TABLE UT > ( > U NUMBER(4), > S NUMBER(4), > G NUMBER(4) > ); > > INSERT INTO UT VALUES(2005,1012,1010); > INSERT INTO UT VALUES(2005,1012,1011); > INSERT INTO UT VALUES(2006,1013,1010); > INSERT INTO UT VALUES(2007,1017,1016); > INSERT INTO UT VALUES(2008,1018,1010); > INSERT INTO UT VALUES(2008,1018,1011); > > INSERT INTO UT VALUES(2009,1019,1016); > INSERT INTO UT VALUES(2001,1020,1010); > INSERT INTO UT VALUES(2001,1020,1011); > > COMMIT; > > =========================================================== > > this query will identify all the security groups and the > minimum security group id of the "identical" one ... > > > SELECT DISTINCT > S2.S ORIGINAL_SG, /* original security group */ > S3.S EQUIV_SG /* equivalent security group */ > FROM ( > SELECT S, COUNT(*) RECS > FROM UT > GROUP BY S > ) S1, /* security groups and their group counts - table1 */ > ( > SELECT S, COUNT(*) RECS > FROM UT > GROUP BY S > ) S2, /* security groups and their group counts - table2 */ > ( > SELECT DISTINCT S > FROM UT > ) S3 /* just the unique security groups */ > WHERE S1.RECS = S2.RECS /* match the sec. groups with the same record > counts */ > AND S1.S <> S2.S /* make sure they are NOT the same security > group */ > AND NOT EXISTS /* make sure they include identical group ids > */ > ( > SELECT G FROM UT WHERE S = S1.S > MINUS > SELECT G FROM UT WHERE S = S2.S > ) > AND S3.S = ( /* see note */ > SELECT MIN(S) > FROM UT > WHERE G IN > ( > SELECT G > FROM UT > WHERE S = S1.S > ) > ) > > /* note : > this is to find the minimum value of the security id which has the same > group > id records as that any of the matching security groups. this minimum > value > can > be used to update the security group ids of all other identical security > groups > at a later point of time > */ > > ============================================================================ > ==== > > you can either change the query to update all the eligible security id to > their corresponding minimum values or generate equivalent update > statements > using this query and run them as a batch ... > > HTH ... > -- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - Choose from over 50 domains or use your own -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Chelur, Jayadas {PBSG} INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
