| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SQL Question
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-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). Received on Thu Apr 03 2003 - 12:14:47 CST
 |
 |