| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL Question
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);
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 */
(
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 ...
-----Original Message-----
Sent: Thursday, April 03, 2003 1:15 PM
To: Multiple recipients of list ORACLE-L
Hi,
Sorry for reposting.
Just wanted to put in a subject...
I have been grappling with this for sometime and thought it will be best for others to take a look at it.
I have a table a_user_groups
USER_ID SECURITY_GROUP_ID GROUP_ID
---------- ----------------- ----------
1005 1012 1010
1005 1012 1011
1006 1013 1010
1007 1017 1016
1008 1018 1010
1008 1018 1011
The security_group_id currently is uniquely generated every time a user
is added and a group_id is associated with the user_id.
For eg: user_id 1005 is associated with groups 1010 and 1011. User 1008
has the same combination but the security_group_id is generated
differently.
The generation happens for C code and there is an option to correct the
problem in the C code but I am trying to see if I can prevent that
The requirement is that user_id 1008 and any other users with the same
group_id combination should have the same security_group_id 1012,
basically the first occurrence for the combination.
In the case of user_id 1006 the value for security_group_id is 1013 and
the group_id the user_id belongs to is 1016. So all the user_ids with a
group_id association
of 1016 (1016 in a combination does not count) down the line will have to
be updated to 1013.
I found a solution for the case where I associate a group to a user_id in this existing table by creating another table that converted the above table into a hierarchy
Table b_hier_user_groups
USER_ID CGID PARENT_VALUE CHILD_VALUE ---------- ---------- ------------ -----------
1005 1012 1010
1005 1012 1010 1011
1006 1013 1010
1007 1017 1016
1008 1018 1010
1008 1018 1010 1011
Then using a PL/SQL script I generated the tree using sys_connect_by_path
I determined if the user had a path that already.
For eg: in the above case if the user 1006 was being associated with
group_id 1011, then I would check the exsiting trees to see if there was
a path already as in 1010,1011.
In this case it does exist and the cgid (equivalent to security_group_id
in the above table) is 1012 and update the user_id 1006 to cgid 1012. But
I am not for some reason able
to apply this solution to the existing rows. I have a feeling that I am
missing something simple....
The requirement that I am grappling with is to update the values in the existing table. I can get the table b_hier_user_groups created from the a_user_groups.
Please let me know if you need more information The table structures are as below
a_user_groups
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NOT NULL NUMBER SECURITY_GROUP_ID NOT NULL NUMBER GROUP_ID NOT NULL NUMBER
Table b_hier_user_groups
Name Null? Type ----------------------------------------- -------- ---------------------------- USER_ID NUMBER CGID ---> same as security_Group_id from above) NUMBER PARENT_VALUE NUMBER CHILD_VALUE NUMBER
Thanks for your time and help in advance.
Regards,
Madhavan
http://www.dpapps.com
-- Madhavan Amruthur DecisionPoint Applications -- http://www.fastmail.fm - I mean, what is it about a decent email service? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Madhavan Amruthur INET: mad5698_at_fastmail.fm 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Apr 03 2003 - 14:28:42 CST
(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: jayadas.chelur_at_pepsi.com 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: ListGuru_at_fatcity.com (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).
 |
 |