8 Replies Latest reply: Jan 4, 2013 11:21 AM by rp0428 RSS

    Query or functions to achieve this logic ??  Please help...

    pbo
      Hi all,

      I've a table "permission" with the following data

      Name Permission
      A Read
      B Read
      A Write
      C Execute
      B Execute

      There can be only three permissions for a user - Read , write and execute

      Following is the required o/p : ( if the user has a permission (r/w/x) , the corresponding value will be 1 , otherwise it is 0 )

      Name Read Write Execute
      A 1 1 0
      B 1 0 1
      C 0 0 1

      Are there any standard functions in Oracle 10 to achieve this o/p ? Or Please provide a query to achieve this ...

      Thanks in Advance,
      Praneeth.

      Edited by: pbo on Jan 3, 2013 8:58 PM
        • 1. Re: Query or functions to achieve this logic ??  Please help...
          jeneesh
          select name,
             nvl(max(decode(permission,'Read',1,0)),0) read,
             nvl(max(decode(permission,'Write',1,0)),0) write,
             nvl(max(decode(permission,'Execute',1,0)),0) execute
          from permission
          group by name;
          • 2. Re: Query or functions to achieve this logic ??  Please help...
            pbo
            Thanks for the reply...
            But is it possible to achieve this without using DECODE or IF/THEN ??
            • 3. Re: Query or functions to achieve this logic ??  Please help...
              Purvesh K
              One way of doing it:
              with data as
              (
                select 'A' name, 'Read' perm from dual union all
                select 'B' name, 'Read' perm from dual union all
                select 'A' name, 'Write' perm from dual union all
                select 'C' name, 'Execute' perm from dual union all
                select 'B' name, 'Execute' perm from dual
              )
              --End of sample data
              select name,
                     max(decode(perm, 'Read', 1, 0)) read,
                     max(decode(perm, 'Write', 1, 0)) write,
                     max(decode(perm, 'Execute', 1, 0)) read
                from data --> You can use your table name instead of sample table
               group by name;
              
              NAME READ                   WRITE                  READ                   
              ---- ---------------------- ---------------------- ---------------------- 
              A    1                      1                      0                      
              B    1                      0                      1                      
              C    0                      0                      1
              • 4. Re: Query or functions to achieve this logic ??  Please help...
                Purvesh K
                jeneesh wrote:
                select name,
                nvl(max(decode(permission,'Read',1,0)),0) read,
                nvl(max(decode(permission,'Write',1,0)),0) write,
                nvl(max(decode(permission,'Execute',1,0)),0) execute
                from permission
                group by name;
                An NVL would not be required, right? Decode handles NULL.
                • 5. Re: Query or functions to achieve this logic ??  Please help...
                  jeneesh
                  pbo wrote:
                  Thanks for the reply...
                  But is it possible to achieve this without using DECODE or IF/THEN ??
                  SQL> with permission
                    2  as
                    3  (
                    4  select 'A' name, 'Read' permission from dual union all
                    5  select 'B', 'Read' from dual union all
                    6  select 'A', 'Write' from dual union all
                    7  select 'C', 'Execute'  from dual union all
                    8  select 'B', 'Execute' from dual
                    9  ),
                   10  reqd_data as
                   11  (
                   12   select name,permission
                   13   from permission
                   14  )
                   15  select *
                   16  from reqd_data
                   17  pivot
                   18    (
                   19     count(*) for permission in ('Read','Write','Execute')
                   20    );
                  
                  N     'Read'    'Write'  'Execute'
                  - ---------- ---------- ----------
                  A          1          1          0
                  B          1          0          1
                  C          0          0          1
                  • 6. Re: Query or functions to achieve this logic ??  Please help...
                    Purvesh K
                    pbo wrote:
                    Thanks for the reply...
                    But is it possible to achieve this without using DECODE or IF/THEN ??
                    You have alternative of PIVOT, introduced in Oracle 11g, but would not be helpful as you are still on Oracle 10g.

                    Jeneesh has shown you the demonstration.
                    • 7. Re: Query or functions to achieve this logic ??  Please help...
                      EdStevens
                      pbo wrote:
                      Thanks for the reply...
                      But is it possible to achieve this without using DECODE or IF/THEN ??
                      and what's wrong with using DECODE or IF/THEN ??


                      IF you find a proposed solution unacceptable, you need to state WHY so that people will (1) know the parameters of an acceptable solution, or (2) be able to explain why your objections are unfounded.
                      • 8. Re: Query or functions to achieve this logic ??  Please help...
                        rp0428
                        >
                        IF you find a proposed solution unacceptable, you need to state WHY so that people will (1) know the parameters of an acceptable solution, or (2) be able to explain why your objections are unfounded.
                        Especially since OP asked for standard functions to begin with.
                        >
                        Are there any standard functions in Oracle 10 to achieve this o/p ?