Re: SQL meaning

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'))
                   ))

TIA Alex

On Tue, Oct 28, 2008 at 4:29 PM, Stephane Faroult <sfaroult_at_roughsea.com>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
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l