Thanks Stephane So the query cannot be wriiten as follows? SELECT userobj.ID, NAME, summary FROM userobj WHERE userobj.NAME LIKE 'A%' *AND attr1 IN ('#ID#TOP')* AND userobj.ID IN ( SELECT ID FROM userattr WHERE ( attr1 IN ( SELECT ID FROM orgattr WHERE orgattr.attrname = 'CONTAINEDBYOBJECTGROUP' AND attrval IN ('#ID#TOP')) )) ORDER BY userobj.ID TIA Alex On Tue, Oct 28, 2008 at 4:29 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote: > Alex, > > No, you can't remove the condition. You have attrl as a user attribute, > as well as an "org" (group, presumably) attribute. I wouldn' have designed > like this but basically the condition says "the user has this characteristic > or s/he belongs to a group that has this characteristic". Something akin > to "the user has this privilege or has a role that has this privilege". > > HTH > > S Faroult > > > > > > *On Mar Oct 28 15:57 , amonte sent: > > * > > Hi > > I have the following query for an Identity Management Tool: > > SELECT userobj.ID, NAME, summary > FROM userobj > WHERE userobj.NAME LIKE 'A%' > AND userobj.ID IN ( > SELECT ID > FROM userattr > WHERE ( attr1 IN ('#ID#TOP') > OR attr1 IN ( > SELECT ID > FROM orgattr > WHERE orgattr.attrname = > 'CONTAINEDBYOBJECTGROUP' > AND attrval IN ('#ID#TOP')) > )) > ORDER BY userobj.ID > > > select table_name, num_rows, last_analyzed from dba_tables where table_name > in > ('USEROBJ', 'USERATTR', 'ORGATTR'); > > TABLE_NAME NUM_ROWS LAST_ANAL > ------------------------------ ---------- --------- > USERATTR 1851675 20-OCT-08 > USEROBJ 64972 20-OCT-08 > ORGATTR 2251 20-OCT-08 > > I am having difficulty understanding the query, for example the condition " > attr1 IN ('#ID#TOP') " can it be taken out to the outer query and not used > in the subquery? > > > TIA > > Alex > > > >