8 Replies Latest reply on Aug 2, 2020 4:24 PM by user517202

    Need Help in Query

    user517202

      Requirement:

      Display only one role value for each user, if user have multiple DB role value, then display PAD_ADMIN first,

      if not then PERFORMANCE_ROLE. If user do not have either, display “NONE”

       

      I tried below query and is not working

       

      select au.user_id, drp.granted_role

      from DBA_ROLE_PRIVS_TEST drp,ref_ausersec_profile au

      where drp.grantee=au.user_id

      and au.subsystem = 'PAD'

      and

      case when drp.granted_role in ('PAD_ADMIN') then 1

      when drp.granted_role in ('PERFORMANCE_ROLE') and drp.granted_role not in ('PAD_ADMIN') then 1

      else 0

      END = 1

      order by au.last_name

       

      Result now showing 4 records as below

        

      USER_IDGRANTED_ROLE
      HELEN_MILLERPERFORMANCE_ROLE
      VIKAS_DUBEYPERFORMANCE_ROLE
      NATASHA_PAULPAD_ADMIN
      NATASHA_PAULPERFORMANCE_ROLE

       

      I want the NATASHA_PAUL record to come only once having PAD_ADMIN role

        • 1. Re: Need Help in Query
          Saubhik

          Requirement:

          Display only one role value for each user, if user have multiple DB role value, then display PAD_ADMIN first,

          if not then PERFORMANCE_ROLE. If user do not have either, display “NONE”

           

          I tried below query and is not working

           

          select au.user_id, drp.granted_role

          from DBA_ROLE_PRIVS_TEST drp,ref_ausersec_profile au

          where drp.grantee=au.user_id

          and au.subsystem = 'PAD'

          and

          case when drp.granted_role in ('PAD_ADMIN') then 1

          when drp.granted_role in ('PERFORMANCE_ROLE') and drp.granted_role not in ('PAD_ADMIN') then 1

          else 0

          END = 1

          order by au.last_name

           

          Result now showing 4 records as below

            

          USER_IDGRANTED_ROLE
          HELEN_MILLERPERFORMANCE_ROLE
          VIKAS_DUBEYPERFORMANCE_ROLE
          NATASHA_PAULPAD_ADMIN
          NATASHA_PAULPERFORMANCE_ROLE

           

          I want the NATASHA_PAUL record to come only once having PAD_ADMIN role

          Please read this: Re: 2. How do I ask a question on the forums?

          Post some CREATE table and INSERT statements for your test data, post the output and explain it in plain simple English. I know DBA_ROLES_PRIVS exists in every Oracle database but not DBA_ROLES_PRIVS_TEST and with your systems data. So, it is necessary to mimic your setup.

          • 2. Re: Need Help in Query
            Frank Kulash

            Hi

             

            So, you want the "best" role for each user, where 'PAD_ADMIN' is "better" than 'PERFORMANCE_ROLE', and all the other roles are worse.

            That sounds like a job for a Top-N Query, like this:

            WITH    got_rnk    AS

            (

                SELECT  au.user_id, au.last_name

                ,       drp.granted_role

                ,       ROW_NUMBER () OVER ( PARTITION BY  au.user_id

                                             ORDER BY      CASE  drp.granted_role

                                                               WHEN  'PAD_ADMIN'         THEN 'A'

                                                               WHEN  'PERFORMANCE_ROLE'  THEN 'B'

                                                                                         ELSE 'C'

                                                           END

                                           )  AS rnk

                FROM    dba_role_privs_test   drp

                ,       ref_ausersec_profile  au  -- or use ANSI join syntax

                WHERE   au.user_id    = drp.grantee

                AND     au.subsystem  = 'PAD'

            )

            SELECT    user_id

            ,         CASE

                          WHEN  granted_role IN ('PAD_ADMIN', 'PERFORMANCE_ROLE')

                          THEN  granted_role

                          ELSE  'NONE'

                      END  AS granted_role

            FROM      got_rnk

            WHERE     rnk  = 1

            ORDER BY  au.last_name

            ;

            If you'd care to post CREATE TABLE and INSERT statements for your sample data, then I could test this.

            • 3. Re: Need Help in Query
              Frank Kulash

              By the way:

              user517202 wrote:

              ...

              and

              case when drp.granted_role in ('PAD_ADMIN') then 1

              when drp.granted_role in ('PERFORMANCE_ROLE') and drp.granted_role not in ('PAD_ADMIN') then 1

              else 0

              END = 1

              ...

              WHEN clauses are evaluated in order.  As soon as one of the WHEN clause evaluates to TRUE, the corresponding THEN expression is returned and the CASE expression is finished; it doesn't even look at the remaining clauses..  That means, in the example above that when you compare granted_role to 'PERFORMANCE_ROLE', you already know that granted_role is not 'PAD_ADMIN', so the condition above could be written more simply:

              ...

              AND  CASE

                      WHEN  drp_granted_role  IN ('PAD_ADMIN')         THEN 1

                      WHEN  drp_granted_ROLE  IN ('PERFORMANCE_ROLE')  THEN 1

                                                                       ELSE 0

                   END  = 1

              or, even more simply:

              AND  CASE

                       WHEN  drp_granted_role  IN ('PAD_ADMIN', 'PERFORMANCE_ROLE')

                       THEN  1

                       ELSE  0

                   END  = 1

              or, even better still:

              AND  drp_granted_role  IN ('PAD_ADMIN', 'PERFORMANCE_ROLE')

              • 4. Re: Need Help in Query
                user517202

                Frank,

                Awesome, Thanks, your query works

                Any other ways to do this other than WITH clause (just checking and curious)

                 

                BUT still I created a table and inserts for you (removed last_name and subsystem and made it simple)

                 

                create table GRANTED_ROLES

                (GRANTEE varchar2(100),

                GRANTED_ROLE varchar2(100));

                 

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','RPES_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','PAD_ADMIN2');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','HRD_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('HELEN_MILLER','PERFORMANCE_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('VIKAS_DUBEY','ETHICS_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('VIKAS_DUBEY','RPES_VIEW_ONLY_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','PERFORMANCE_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('VIKAS_DUBEY','LEAVE_TRANSFER_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('VIKAS_DUBEY','PERFORMANCE_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('VIKAS_DUBEY','HRD_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','PATS_READ_WRITE_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','TED_ROLE');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('NATASHA_PAUL','PAD_ADMIN');

                Insert into GRANTED_ROLES (GRANTEE,GRANTED_ROLE) values ('CASEY_JAMES',NULL);

                 

                create table USER_PROFILE

                (USER_ID varchar2(100));

                 

                Insert into USER_PROFILE (USER_ID) values ('HELEN_MILLER');

                Insert into USER_PROFILE (USER_ID) values ('VIKAS_DUBEY');

                Insert into USER_PROFILE (USER_ID) values ('NATASHA_PAUL');

                Insert into USER_PROFILE (USER_ID) values ('CASEY_JAMES');

                 

                COMMIT

                • 5. Re: Need Help in Query
                  Frank Kulash

                  Hi,

                  user517202 wrote:

                  ...

                  Any other ways to do this other than WITH clause (just checking and curious)

                  ...

                  Sure, there are lots of ways.  Which way is best for your requirements?  That depends on your requirements.  Why do you want to avoid the WITH clause?

                   

                  You could use an in-line view instead of the WITH clause in this case.  (You can usually substitute in-line views for WITH clauses.)

                  • 6. Re: Need Help in Query
                    user517202

                    That is  okay, Thanks a lot for the answer, I really appreciate

                    • 7. Re: Need Help in Query
                      mathguy

                      I would do the work in a single, aggregate query (then outer join to the USER_PROFILE table to include all users). Analytic functions are fine - you can do a lot of things with them, which you can't do with aggregates - but when they do the same job as aggregate functions, analytic functions are typically slower - and sometimes significantly so.

                       

                      In the aggregate query you only need to consider rows where the role is one of the two "distinguished" ones, and it is best to filter that way so that the aggregation operates only on a small(er) number of rows. This could have been done in the analytic function based approach too, to lighten the workload of the analytic function.

                       

                      I used the data you provided in Reply 4.

                       

                      select up.user_id, nvl(g.granted_role, 'NONE') as granted_role

                      from   user_profile up    left outer join

                             ( select grantee,

                                      min(granted_role) keep (dense_rank first

                                          order by decode(granted_role, 'PAD_ADMIN', 1, 'PERFORMANCE_ROLE', 2)) as granted_role

                               from   granted_roles

                               where  granted_role in ('PAD_ADMIN', 'PERFORMANCE_ROLE')

                               group  by grantee

                             ) g                on up.user_id = g.grantee

                      order  by user_id   -- if needed

                      ;

                       

                       

                      USER_ID              GRANTED_ROLE      

                      -------------------- --------------------

                      CASEY_JAMES          NONE              

                      HELEN_MILLER         PERFORMANCE_ROLE  

                      NATASHA_PAUL         PAD_ADMIN        

                      VIKAS_DUBEY          PERFORMANCE_ROLE

                      1 person found this helpful
                      • 8. Re: Need Help in Query
                        user517202

                        Great. Thanks a lot mathguy. I appreciate