Re: SQL meaning

  • From: amonte <ax.mount@xxxxxxxxx>
  • To: sfaroult@xxxxxxxxxxxx
  • Date: Tue, 28 Oct 2008 16:48:21 +0100

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

Other related posts: